Problem
I’d want to set the auto increment field of a table to a specific value, thus I tried:
ALTER TABLE product AUTO_INCREMENT = 1453
AND
ALTER SEQUENCE product RESTART WITH 1453;
ERROR: relation "your_sequence_name" does not exist
I’m not familiar with Postgres:(
I have a table product with fields for Id and Name.
Asked by Rad
Solution #1
If you used an id column in the table product, the sequence is termed product id seq (that is, $table $column seq).
You’ll need the ALTER SEQUENCE command:
ALTER SEQUENCE product_id_seq RESTART WITH 1453
Using the ds command in psql, you may see the sequences in your database. The nextval(…) method will also specify the sequence name if you do d product and look at the default constraint for your column.
Answered by araqnid
Solution #2
This is done for you automatically with the following command: This will also remove all of the table’s data. So be cautious.
TRUNCATE TABLE someTable RESTART IDENTITY;
Answered by Loolooii
Solution #3
If your product table’s sequence is product id seq, then this is the command you’re looking for:
ALTER SEQUENCE product_id_seq RESTART WITH 1453;
Answered by matt snider
Solution #4
To start the sequence counter, do the following:
setval('product_id_seq', 1453);
Use the pg get serial sequence method if you don’t know the sequence name:
select pg_get_serial_sequence('product', 'id');
pg_get_serial_sequence
------------------------
public.product_id_seq
The table name and column name are the parameters.
Alternatively, just type 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 |
Answered by Clodoaldo Neto
Solution #5
-- Change the starting value of the sequence
ALTER SEQUENCE project_id_seq RESTART 3000;
The same, but with a twist:
SELECT SETVAL('project_id_seq', (SELECT MAX(id) + 1 FROM project));
Although ECT is upsetting, it is effective.
Source: https://kylewbanks.com/blog/Adding-or-Modifying-a-PostgreSQL-Sequence-Auto-Increment
Answered by Wiwwil
Post is based on https://stackoverflow.com/questions/5342440/reset-auto-increment-counter-in-postgres