I would like to force the auto increment field of a table to some value, I tried with this:
ALTER TABLE product AUTO_INCREMENT = 1453
AND
ALTER SEQUENCE product RESTART WITH 1453;
ERROR: relation "your_sequence_name" does not exist
I'm new to postgres :(
I have a table product
with Id
and name
field
This question is related to
sql
postgresql
reset
auto-increment
If you created the table product
with an id
column, then the sequence is not simply called product
, but rather product_id_seq
(that is, ${table}_${column}_seq
).
This is the ALTER SEQUENCE
command you need:
ALTER SEQUENCE product_id_seq RESTART WITH 1453
You can see the sequences in your database using the \ds
command in psql. If you do \d product
and look at the default constraint for your column, the nextval(...)
call will specify the sequence name too.
Here is the command that you are looking for, assuming your sequence for the product table is product_id_seq:
ALTER SEQUENCE product_id_seq RESTART WITH 1453;
To set the sequence counter:
setval('product_id_seq', 1453);
If you don't know the sequence name use the pg_get_serial_sequence
function:
select pg_get_serial_sequence('product', 'id');
pg_get_serial_sequence
------------------------
public.product_id_seq
The parameters are the table name and the column name.
Or just issue a \d product
at the psql
prompt:
=> \d product
Table "public.product"
Column | Type | Modifiers
--------+---------+------------------------------------------------------
id | integer | not null default nextval('product_id_seq'::regclass)
name | text |
Note that if you have table name with '_', it is removed in sequence name.
For example, table name: user_tokens column: id Sequence name: usertokens_id_seq
-- Change the starting value of the sequence
ALTER SEQUENCE project_id_seq RESTART 3000;
Same but dynamic :
SELECT SETVAL('project_id_seq', (SELECT MAX(id) + 1 FROM project));
I agree the use of a SELECT
is disturbing but it works.
Source: https://kylewbanks.com/blog/Adding-or-Modifying-a-PostgreSQL-Sequence-Auto-Increment
Use this query to check what is the Sequence Key with Schema and Table,
SELECT pg_get_serial_sequence('"SchemaName"."TableName"', 'KeyColumnName'); // output: "SequenceKey"
Use this query increase increment value one by one,
SELECT nextval('"SchemaName"."SequenceKey"'::regclass); // output 110
When inserting to table next incremented value will be used as the key (111).
Use this query to set specific value as the incremented value
SELECT setval('"SchemaName"."SequenceKey"', 120);
When inserting to table next incremented value will be used as the key (121).
To reset the auto increment you have to get your sequence name by using following query.
Syntax:
SELECT pg_get_serial_sequence(‘tablename’, ‘ columnname‘);
Example:
SELECT pg_get_serial_sequence('demo', 'autoid');
The query will return the sequence name of autoid as "Demo_autoid_seq" Then use the following query to reset the autoid
Syntax:
ALTER SEQUENCE sequenceName RESTART WITH value;
Example:
ALTER SEQUENCE "Demo_autoid_seq" RESTART WITH 1453;
To get sequence id use
SELECT pg_get_serial_sequence('tableName', 'ColumnName');
This will gives you sequesce id as tableName_ColumnName_seq
To Get Last seed number use
select currval(pg_get_serial_sequence('tableName', 'ColumnName'));
or if you know sequence id already use it directly.
select currval(tableName_ColumnName_seq);
It will gives you last seed number
To Reset seed number use
ALTER SEQUENCE tableName_ColumnName_seq RESTART WITH 45
Converted from comment for the sake of visitor's convenience
It's not clear from this message what the correct syntax is. It is:
ALTER SEQUENCE product_id_seq RESTART WITH 1453;
if you want to Reset auto increment from GUI, then follow this steps.
The following command does this automatically for you: This will also delete all the data in the table. So be careful.
TRUNCATE TABLE someTable RESTART IDENTITY;
If you have a table with an IDENTITY column that you want to reset the next value for you can use the following command:
ALTER TABLE <table name>
ALTER COLUMN <column name>
RESTART WITH <new value to restart with>;
Source: Stackoverflow.com