[postgresql] postgresql duplicate key violates unique constraint

Hi i have a question i know this was posted many times but i didn't find an answer to my problem. The problem is that i have a table and a column "id" i want it to be unique number just as normal. This type of column is serial and the next value after each insert is comming from a sequence so everything seems to be all right but it still sometimes showing this error. I dont know why ? In the documentation it is writtne the sequence is fool prof and always works. If I add a UNIQUE constraint to that column will it thelp ? I worked before many times on Postres but this error is showing for me for the first time. I did everything as normal and i never had this problem before. Can you help me to find the answer that can be used in the future for all tables that will be created ? Lets say we have something easy like this:

CREATE TABLE comments
(
  id serial NOT NULL,
  some_column text NOT NULL,
  CONSTRAINT id_pkey PRIMARY KEY (id)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE interesting.comments OWNER TO postgres;

If i add:

ALTER TABLE comments ADD CONSTRAINT id_id_key UNIQUE(id)

Will if be enought or is there some other thing that should be done ?

For future searchs, use ON CONFLICT DO NOTHING.

I have similar problem but I solved it by removing all the foreign key in my Postgresql

Intro

I also encountered this problem and the solution proposed by @adamo was basically the right solution. However, I had to invest a lot of time in the details, which is why I am now writing a new answer in order to save this time for others.

Case

My case was as follows: There was a table that was filled with data using an app. Now a new entry had to be inserted manually via SQL. After that the sequence was out of sync and no more records could be inserted via the app.

Solution

As mentioned in the answer from @adamo, the sequence must be synchronized manually. For this purpose the name of the sequence is needed. For Postgres, the name of the sequence can be determined with the command PG_GET_SERIAL_SEQUENCE. Most examples use lower case table names. In my case the tables were created by an ORM middleware (like Hibernate or Entity Framework Core etc.) and their names all started with a capital letter.

In an e-mail from 2004 (link) I got the right hint.

(Let's assume for all examples, that Foo is the table's name and Foo_id the related column.)

Command to get the sequence name:

SELECT PG_GET_SERIAL_SEQUENCE('"Foo"', 'Foo_id');

So, the table name must be in double quotes, surrounded by single quotes.

1. Validate, that the sequence is out-of-sync

SELECT CURRVAL(PG_GET_SERIAL_SEQUENCE('"Foo"', 'Foo_id')) AS "Current Value", MAX("Foo_id") AS "Max Value" FROM "Foo";

When the Current Value is less than Max Value, your sequence is out-of-sync.

2. Correction

SELECT SETVAL((SELECT PG_GET_SERIAL_SEQUENCE('"Foo"', 'Foo_id')), (SELECT (MAX("Foo_id") + 1) FROM "Foo"), FALSE);

This article explains that your sequence might be out of sync and that you have to manually bring it back in sync.

An excerpt from the article in case the URL changes:

If you get this message when trying to insert data into a PostgreSQL database:

ERROR:  duplicate key violates unique constraint

That likely means that the primary key sequence in the table you're working with has somehow become out of sync, likely because of a mass import process (or something along those lines). Call it a "bug by design", but it seems that you have to manually reset the a primary key index after restoring from a dump file. At any rate, to see if your values are out of sync, run these two commands:

SELECT MAX(the_primary_key) FROM the_table;   
SELECT nextval('the_primary_key_sequence');

If the first value is higher than the second value, your sequence is out of sync. Back up your PG database (just in case), then run thisL

SELECT setval('the_primary_key_sequence', (SELECT MAX(the_primary_key) FROM the_table)+1);

That will set the sequence to the next available value that's higher than any existing primary key in the sequence.

In my case carate table script is:

CREATE TABLE public."Survey_symptom_binds"
(
    id integer NOT NULL DEFAULT nextval('"Survey_symptom_binds_id_seq"'::regclass),
    survey_id integer,
    "order" smallint,
    symptom_id integer,
    CONSTRAINT "Survey_symptom_binds_pkey" PRIMARY KEY (id)
)

SO:

SELECT nextval('"Survey_symptom_binds_id_seq"'::regclass),
       MAX(id) 
  FROM public."Survey_symptom_binds"; 
  
SELECT nextval('"Survey_symptom_binds_id_seq"'::regclass) less than MAX(id) !!!

Try to fix the proble:

SELECT setval('"Survey_symptom_binds_id_seq"', (SELECT MAX(id) FROM public."Survey_symptom_binds")+1);

Good Luck every one!

From http://www.postgresql.org/docs/current/interactive/datatype.html

Note: Prior to PostgreSQL 7.3, serial implied UNIQUE. This is no longer automatic. If you wish a serial column to be in a unique constraint or a primary key, it must now be specified, same as with any other data type.

Referrence - https://www.calazan.com/how-to-reset-the-primary-key-sequence-in-postgresql-with-django/

I had the same problem try this: python manage.py sqlsequencereset table_name

Eg:

python manage.py sqlsequencereset auth

you need to run this in production settings(if you have) and you need Postgres installed to run this on the server

The primary key is already protecting you from inserting duplicate values, as you're experiencing when you get that error. Adding another unique constraint isn't necessary to do that.

The "duplicate key" error is telling you that the work was not done because it would produce a duplicate key, not that it discovered a duplicate key already commited to the table.