Postgres sequence

Postgres’ sequence is similar to MySQL’s auto increment IDs.

Once in a while, you may get this error: duplicate key value violates unique constraint “your_primary_key”

Basically, that means postgres is trying to insert a new record to a table, but fails because there is already a row having the same new ID. But how can that be? You may ask. Chances are that your insert statement doesn’t even have the auto-increment ID!

Well… what’s happening here is that, postgres remembers from before that the next auto-increment number is say 25. However, for whatever reasons, there are rows already going up to 100, possibly due to manually inserting. You can confirm that by running select max(id) from your_table_name. All you need to do is to tell postgres to remember the next sequence number is 101 instead.

First login to the postgres shell.

psql -U your_postgres_user_name

Then look at your table definition.

d your_table_name

Look at the 3rd column Modifiers that has something similar to this string: not null default nextval(‘charts_id_seq’::regclass).

You can see that our target sequence name is charts_id_seq.

Then just assign the next available value to it.

ALTER SEQUENCE charts_id_seq RESTART WITH 101;

Then you are done! Ctrl-D to exit out the postgres shell.