Reset auto increment counter in postgres

sql postgresql reset auto-increment
Rad picture Rad · Mar 17, 2011 · Viewed 202.2k times · Source

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

Answer

araqnid picture araqnid · Mar 17, 2011

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.

Related questions

Select first row in each GROUP BY group?

As the title suggests, I'd like to select the first row of each set of rows grouped with a GROUP BY. Specifically, if I've got a purchases table that looks like this: SELECT * FROM purchases; My Output: id | customer | total …

Read More
How do I (or can I) SELECT DISTINCT on multiple columns?

I need to retrieve all rows from a table where 2 columns combined are all different. So I want all the sales that do not have any other sales that happened on the same day for the same price. The sales …

Read More
How to select the nth row in a SQL database table?

I'm interested in learning some (ideally) database agnostic ways of selecting the nth row from a database table. It would also be interesting to see how this can be achieved using the native functionality of the following databases: SQL Server …

Read More