Reputation: 48503
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
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
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
Reputation: 183
I think there is a simpler way:
ALTER SEQUENCE "seq_product_id" RESTART WITH 10
Upvotes: 13