Search code examples
sqlpostgresqlresetauto-increment

Reset auto increment counter in postgres

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 have a table product with Id and name field

Solution

  • 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.