1

I have a problem, when inserting a new row in the table I get the error: "duplicate key violates unique constraint".

After some analysis, I found this solution that helps to find out that the sequence is out of sync.

I used these two queries to find out if my id is out of sync:

SELECT MAX(id) FROM schema.table t; -- Value: 456

SELECT nextval(PG_GET_SERIAL_SEQUENCE('"table"', 'id')); -- Value: 462

They say that if the ID of the first query is greater than the ID of the second, it is out of sync.

But in my case, the ID of the first one is lower than the ID of the second one.

What can I conclude from here and how can I resolve my error when inserting a newline?

Note:

select * from pg_sequences;

With this query, I just discovered that I have 2 sequences with the same name: one is table_id_seq and another is table_id_seq1, could this be a problem?

Gustavo Oliveira
  • 61
  • 7
  • 2
    1) Your sequence is fine. Sequences are not guaranteed to be gapless. Also they do not rollback when touched so they keep advancing whether the value is used or not. In fact the `nextval()` will do just that per [Seq functions](https://www.postgresql.org/docs/14/functions-sequence.html). 2) The sequence names you show are different so I am not sure why you say they are the same? 3) The most common cause of the the error is manually supplying an id instead of letting the sequence do it. – Adrian Klaver Jul 25 '22 at 17:26
  • Thank you @AdrianKlaver, with your response I already found the root cause of my error! You're right on the 3rd – Gustavo Oliveira Jul 26 '22 at 08:12

1 Answers1

1

After more analysis, I could check and confirm that my sequence was out of sync.

I've followed this with some adjustments.

Commands to check if the problem is out of sync:

SELECT MAX(id) FROM schema.table b ; -- 456 rows

SELECT nextval(PG_GET_SERIAL_SEQUENCE('"table"', 'id')); -- 462 rows

Like they said in the post: If the first value is higher than the second value, your sequence is out of sync.

This was the final command that I had to run to update the sequence and fix my bug:

SELECT setval(PG_GET_SERIAL_SEQUENCE('"table"', 'id'), (SELECT MAX(id) FROM schema.table b)+1);
Gustavo Oliveira
  • 61
  • 7
  • Shouldn't it be `SELECT MAX(id) FROM schema.table` without the b? There were too many pending edits on the answer for me to correct it – Arturo Mendes Mar 08 '23 at 18:46