postgresql duplicate key violates unique constraint

Question

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

User · Answer

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.

User · Answer

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

User · Answer

In my case carate table script is  CREATE TABLE public  quot Survey symptom binds quot        id integer NOT NULL DEFAULT nextval   quot Survey symptom binds id seq quot    regclass       survey id integer       quot order quot  smallint      symptom id integer      CONSTRAINT  quot Survey symptom binds pkey quot  PRIMARY KEY  id     SO  SELECT nextval   quot Survey symptom binds id seq quot    regclass          MAX id     FROM public  quot Survey symptom binds quot       SELECT nextval   quot Survey symptom binds id seq quot    regclass  less than MAX id       Try to fix the proble  SELECT setval   quot Survey symptom binds id seq quot     SELECT MAX id  FROM public  quot Survey symptom binds quot   1    Good Luck every one

User · Answer

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

User · Answer

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   quot Foo quot     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   quot Foo quot     Foo id    AS  quot Current Value quot   MAX  quot Foo id quot   AS  quot Max Value quot  FROM  quot Foo quot    When the Current Value is less than Max Value  your sequence is out-of-sync  2  Correction SELECT SETVAL  SELECT PG GET SERIAL SEQUENCE   quot Foo quot     Foo id      SELECT  MAX  quot Foo id quot     1  FROM  quot Foo quot    FALSE

User · Answer

For future searchs  use ON CONFLICT DO NOTHING

User · Answer

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

User · Answer

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

[postgresql] postgresql duplicate key violates unique constraint

Examples related to postgresql