piątek, 5 listopada 2010

Adjusting PostgreSQL sequence

Sometimes (hopefully not in production) you have "low" sequence values but "high" identifiers in the respective table. That usually results in primary key violation on an attempt to insert a new row.

So - if you really do not want to delete the data, you should make the sequence have those "high" values, ideally max(id_from_the_table) + 1.

In PostgreSQL it turns out to be much more elegant than in Oracle, where you can either come up with some PL/SQL FOR loop or use DDL ALTER SEQUENCE statements. The examples have been copied from

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

SELECT setval('foo', 42); Next nextval will return 43

SELECT setval('foo', 42, true); Same as above

SELECT setval('foo', 42, false); Next nextval will return 42