3

I have a table called person with primary key on id; enter image description here

enter image description here

I am trying to insert into this table with:

insert into person (first_name, last_name, email, gender, date_of_birth, country_of_birth) values ('Ellissa', 'Gordge', 'ggordge0@gnu.org', 'Male', '2022-03-19', 'Fiji');

There should not be any ID constraint which are being violated since it is a BIGSERIAL yet I am getting this: enter image description here

It says Key id=(8) already exists and it is incrementing on each attempt to run this command. How can ID already exist? And why is it not incrementing from the bottom of the list?

If i specify the id in the insert statement, with a number which i know is unique it works. I just don't understand why is it not doing it automatically since I am using BIGSERIAL.

Slartibartfast
  • 1,058
  • 4
  • 26
  • 60

2 Answers2

5

Your sequence apparently is out of sync with the values in the column. This can happen when someone did INSERT INTO person(id, …) VALUES (8, …) (or maybe a csv COPY import, or anything else that did provide values for the id column instead of using the default), or when someone did reset the sequence of having inserted data.

You can alter the sequence to fix this:

ALTER SEQUENCE person_id_seq RESTART WITH (SELECT MAX(id)+1 FROM person);

You can set the sequence value to fix this:

SELECT setval('person_id_seq', MAX(id)+1) FROM person;

Also notice that it is recommended to use an identity column rather than a serial one to avoid this kind of problem.

Bergi
  • 630,263
  • 148
  • 957
  • 1,375
  • Its throwing this error: `ERROR: syntax error at or near "(" LINE 1: ALTER SEQUENCE person_id_seq RESTART WITH (SELECT MAX(id)+1 ...` Also could `INCREMENT ` be used here interchanably? Please advise – Slartibartfast Jun 02 '22 at 21:35
  • 1
    Ah, looks like `ALTER SEQUENCE` doesn't support expressions, only hardcoded values like `… RESTART WITH 532;` (you can of course find the maximum manually). Looks like you need to use [the `setval` sequence function](https://www.postgresql.org/docs/current/functions-sequence.html), similar to Pierre's answer: `SELECT setval('person_id_seq', MAX(id)+1) FROM person;` – Bergi Jun 03 '22 at 00:01
  • 1
    No, `INCREMENT` cannot be used, that's for changing the step of the sequence, e.g. to only odd numbers (`INCREMENT BY 2 START WITH 1`) or decrementing sequences (`INCREMENT BY -1 START WITH 100000`). – Bergi Jun 03 '22 at 00:03
3
SELECT pg_catalog.setval(pg_get_serial_sequence('table_name', 'id'), MAX(id)) FROM table_name;

This should kickstart your sequence table back in sync, which should fix everything. Make sure to change 'table_name' to the actual name. Cheers!

BigP
  • 117
  • 7
  • This is easier than the accepted answer since you don't have to look up the name of the primary key sequence; you only have to know the table name – Sean McCarthy Aug 03 '22 at 16:37