CODEWITHSUNDEEP
sqlpostgresqlauto-increment
user984621
user984621

Reputation: 48503

How to set up manually the next value of auto_increment?

I added some rows into the table manually and also I set up the ID (auto_increment) manually. Now when I try to add new row through my app into DB table, to DB table I am getting the error , that the created ID value already exist.

How can I set manually the next ID value (for example, in table I have to IDs, so how to tell to PostgreSQL, that the next ID should be counted since the number 3)?

Upvotes: 18

Views: 22705

Answers (3)

Tatsu
Tatsu

Reputation: 1901

Latest Solution

If you want to see more detailed information about sequences in your database, you can query the pg_sequences system catalog:

SELECT * FROM pg_sequences WHERE sequencename LIKE '%id%';

This query helps you find sequences that might be related to columns named id.

Once you have the correct sequence name , you can use it in your setval function like so:

SELECT setval('public."User_id_seq"', max(id)) FROM "User";

Upvotes: 1

strkol
strkol

Reputation: 2029

http://www.postgresql.org/docs/current/static/functions-sequence.html

select setval('sequence-name', <new-value>);

You can get the sequence name from the the table definition:

id       | integer                | not null default nextval('id_seq'::regclass)

In this case the sequence is named 'id_seq'

Edit (10x to @Glenn):

SELECT setval('id_seq', max(id)) FROM table;

Upvotes: 37

waveiro
waveiro

Reputation: 183

I think there is a simpler way:

ALTER SEQUENCE "seq_product_id"  RESTART WITH 10

Upvotes: 13

Related Questions