15

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)?

yivi
  • 42,438
  • 18
  • 116
  • 138
user984621
  • 46,344
  • 73
  • 224
  • 412

2 Answers2

33

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;
strkol
  • 1,909
  • 15
  • 11
  • 3
    And set to the result of SELECT MAX(id)+1 FROM myTable. – Glenn Apr 12 '12 at 02:53
  • As per documentation setval given two parameters sets the given sequence value to given number max(id) which technically sets the nextval to max(id)+1. In other words you should not use max(id)+1 unless you want to skip the max(id)+1 number, or use false as third parameter. – Miro Nov 18 '20 at 04:09
12

I think there is a simpler way:

ALTER SEQUENCE "seq_product_id"  RESTART WITH 10
waveiro
  • 163
  • 3
  • 10