1

I'm running Postgres10 on PGAdmin4

To see if my database is out of sync, I'm checking my primary key and primary key sequence.

I'm running this query to check primary key:

  • MAX(sid) FROM schema_name.table_name; Returns 1032

    • sid is the primary key

    • schema_name is the schema where my table is located

    • table_name is name of the table where the unique constraint is being violated

I'm running this query to check primary key sequence:

  • SELECT nextval(pg_get_serial_sequence('schema_name.table_name', 'sid')); returns 1042 (current value is 1041).

Referencing this SO: I'm referencing this stack overflow: postgresql duplicate key violates unique constraint

But the post is 9 years old and the solution only checks if the primary key's max is greater than the next value of the sequence (which it isn't in my case).

halfer
  • 19,824
  • 17
  • 99
  • 186
Tim
  • 478
  • 4
  • 15
  • 1
    I don't see what why the primary key and the sequence would have to be related to each other. Are you familiar with `delete`? I also don't see what this check has to do with the title of the post. – Gordon Linoff Jan 16 '20 at 20:49
  • 1
    Rows can be deleted from the table, but the nextval will not change. Do you have any auditing on the table to see if rows were deleted? – scottsaenz Jan 16 '20 at 20:52
  • Why do you care if the `sid` values are serial or not? They just need to be **different**. – The Impaler Jan 16 '20 at 20:55
  • 2
    Beware, [`nextval` modifies the sequence](https://www.postgresql.org/docs/current/functions-sequence.html), so running that command is going to de-sync things anyways. You probably want `currval` instead. That aside, **ignore the apparent value in the sequence**. Consider them "memory addresses", as if using pointers in a language like C++: the 'value' is meaningless, and outside your control. – Clockwork-Muse Jan 16 '20 at 21:35
  • I guess you are using an auto-increment for sid, as said in other comments if you delete some record and than add some others the count of rows is not what you expect. If you want to reuse id in an auto-increment setting try to use a trigger that take the number of rows plus one as sid when you add a record – GJCode Jan 16 '20 at 22:09
  • 1
    Since the sequence is greater than the max id on your table, you must be getting `SQL issue: Duplicate key value violates unique constraint` from some other unique key. Unless you were querying nextval on the sequence enough times now, that the insert is not generating errors anymore. – Scratte Jan 17 '20 at 00:55
  • Thank you to everyone who commented. @GordonLinoff: I see what you are saying now. Sorry, I was blindly following the other post I linked in the question, so primary key and the sequence don't have to be related to each other. The unique constraint in question restricts the columns 'organization_identity' and 'key' (Thinking this means a 'organization_identity' and 'key' pair can't appear twice).So I've wiped this entire table, but I'm still getting this error... Could you give some advice on how to proceed? Sorry for the noob question, I'm a beginner to databases... Thanks for your time – Tim Jan 17 '20 at 22:51

1 Answers1

0

I have run into issues with a key being out of sync with a sequence when a custom program is inserting records records into a table and taking the last_key_value + 1 while another program is using the sequence nextval to insert into the same table.

This can create duplicate key problems.

I would check to make sure you don't have programs with this conflict.

A better way to fully circumvent this type of problem is to use an IDENTITY type column. Though I dont know if Postgres supports this data type.

alexherm
  • 1,362
  • 2
  • 18
  • 31