CODEWITHSUNDEEP
postgresql
red
red

Reputation: 1767

postgresql duplicate key violates unique constraint

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 coming from a sequence so everything seems to be all right but it still sometimes shows this error. I don't know why. In the documentation, it says the sequence is foolproof and always works. If I add a UNIQUE constraint to that column will it help? 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? Let's 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 enough or is there some other thing that should be done?

Upvotes: 175

Views: 451844

Answers (16)

boksiora
boksiora

Reputation: 1198

Here is how to do it for all tables with ID column

DO $$
DECLARE
    tbl RECORD;
    seq_name TEXT;
    max_id BIGINT;
BEGIN
    -- Loop through all tables with an `id` column
    FOR tbl IN
        SELECT table_schema, table_name
        FROM information_schema.columns
        WHERE column_name = 'id'
          AND table_schema NOT IN ('pg_catalog', 'information_schema')
    LOOP
        -- Dynamically construct the sequence name
        seq_name := format('%I.%I_id_seq', tbl.table_schema, tbl.table_name);

        -- Check if the sequence exists
        IF EXISTS (
            SELECT 1
            FROM information_schema.sequences
            WHERE sequence_schema = tbl.table_schema
              AND sequence_name = format('%I_id_seq', tbl.table_name)
        ) THEN
            -- Get the max ID value from the table
            EXECUTE format('SELECT max(id) FROM %I.%I', tbl.table_schema, tbl.table_name)
            INTO max_id;

            -- Use COALESCE to handle NULL values for empty tables
            EXECUTE format(
                'SELECT setval(%L, coalesce(%s, 1), false)',
                seq_name,
                COALESCE(max_id, 0) + 1
            );
        END IF;
    END LOOP;
END $$;

Upvotes: 0

Frank_UK
Frank_UK

Reputation: 1

I've just started messing around in Supabase today, having had limited experience in SQL at Uni + having dabbled with it in previous jobs.

I found that I'd been updating triggers in the wrong way, simply by labling them v2,v3 and so on. Meaning that they were all trying to trigger my db function at the same time which caused my issue.

After deleting the older versions of the trigger I no longer received this error.

Upvotes: 0

adamo
adamo

Reputation: 4032

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(pg_get_serial_sequence('the_table', 'the_primary_key'));

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 this command:

SELECT setval(pg_get_serial_sequence('the_table', 'the_primary_key'), (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.

Upvotes: 315

Jarrod Moldrich
Jarrod Moldrich

Reputation: 805

As @adamo's answer explains, nextval(..) can be out of sync and no longer incrementing upon the max value. In my case it was due to a logical replication and switch-over, where PostgreSQL leaves all sequence numbers as they were at subscription time.

For larger databases, here are some convenient functions for querying which integer primary keys are out of sync, and to update them.

Verify

CREATE OR REPLACE FUNCTION primary_id_sequences_from_all_tables()
RETURNS TABLE (table_name TEXT, column_name TEXT, data_type TEXT, max BIGINT, next BIGINT) AS $$
DECLARE
    rec RECORD;
BEGIN
    FOR rec IN
        SELECT tc.table_name, kcu.column_name, c.data_type
        FROM information_schema.table_constraints AS tc
        JOIN information_schema.key_column_usage AS kcu
            ON tc.constraint_name = kcu.constraint_name
            AND tc.table_schema = kcu.table_schema
        JOIN information_schema.columns AS c
            ON kcu.table_name = c.table_name
            AND kcu.column_name = c.column_name
            AND c.data_type IN ('smallint', 'integer', 'bigint', 'decimal', 'numeric', 'real', 'double precision')
        WHERE tc.constraint_type = 'PRIMARY KEY'
    LOOP
        RETURN QUERY EXECUTE 'SELECT ' || quote_nullable(rec.table_name) || ', ' || quote_nullable(rec.column_name) || ', ' || quote_nullable(rec.data_type) || ', (SELECT COALESCE(MAX(' || rec.column_name || '), 0)::BIGINT FROM ' || rec.table_name || '), (SELECT nextval(pg_get_serial_sequence(' || quote_nullable(rec.table_name) || ', ' || quote_nullable(rec.column_name) || ')))';
    END LOOP;
END;
$$ LANGUAGE plpgsql;

n.b., the function calls nextval which will have a side-effect of incrementing the sequence values. It shouldn't be a problem, but if you want to avoid this you can switch this to pg_sequence_last_value. However this may only show primary keys for which nextval has already been called.

Call the function to verify the list:

SELECT table_name, column_name, data_type, max, next, (max - next) AS diff
  FROM primary_id_sequences_from_all_tables()
  WHERE next IS NOT NULL AND max > next
  ORDER BY diff DESC;

Update

If you are satisfied and wish at your own risk to fudge the nextval() to be max(..) + 1 for all those columns you can run this function:

CREATE OR REPLACE FUNCTION fixup_primary_id_sequences_in_all_tables()
RETURNS TABLE (table_name TEXT, column_name TEXT, max BIGINT, next BIGINT, set_to BIGINT) AS $$
DECLARE
    rec RECORD;
BEGIN
    FOR rec IN
        SELECT seqs.table_name, seqs.column_name, seqs.max, seqs.next
        FROM primary_id_sequences_from_all_tables() AS seqs
        WHERE seqs.next IS NOT NULL AND seqs.max > seqs.next
    LOOP
        RETURN QUERY EXECUTE 'SELECT ' || quote_nullable(rec.table_name) || ', ' || quote_nullable(rec.column_name) || ', ' || rec.max || '::BIGINT, ' || rec.next || '::BIGINT, setval(pg_get_serial_sequence(' || quote_nullable(rec.table_name) || ', ' || quote_nullable(rec.column_name) || '), (SELECT MAX(' || rec.column_name || ') FROM ' || rec.table_name || ')+1) AS set_to';
    END LOOP;
END;
$$ LANGUAGE plpgsql;

... with this line:

SELECT * FROM fixup_primary_id_sequences_in_all_tables()

Upvotes: 3

Ahmed Shehata
Ahmed Shehata

Reputation: 21

try that CLI

it's just a suggestion to enhance the adamo code (thanks a lot adamo)

SELECT setval('tableName_columnName_seq', (SELECT MAX(columnName) FROM tableName));

Upvotes: 0

Salman
Salman

Reputation: 1030

Replace the table_name to your actual name of the table.

  1. Gives the current last id for the table. Note it that for next step.
SELECT MAX(id) FROM table_name;
  1. Get the next id sequence according to postgresql. Make sure this id is higher than the current max id we get from step 1
SELECT nextVal('"table_name_id_seq"');
  1. if it's not higher than then use this step 3 to update the next sequence.
SELECT setval('"table_name_id_seq"', (SELECT MAX(id) FROM table_name)+1);

Upvotes: 33

Raphael Fernandes
Raphael Fernandes

Reputation: 861

For programatically solution at Django. Based on Paolo Melchiorre's answer, I wrote a chunk as a function to be called before any .save()

from django.db import connection
def setSqlCursor(db_table):
    sql = """SELECT pg_catalog.setval(pg_get_serial_sequence('"""+db_table+"""', 'id'), MAX(id)) FROM """+db_table+""";"""
    with connection.cursor() as cursor:
        cursor.execute(sql)

Upvotes: -3

lila montenegro
lila montenegro

Reputation: 9

I had the same problem. It was because of the type of my relations. I had a table property which related to both states and cities. So, at first I had a relation from property to states as OneToOne, and the same for cities. And I had the same error "duplicate key violates unique constraint". That means that: I can only have one property related to one state and city. But that doesnt make sense, because a city can have multiple properties. So the problem is the relation. The relation should be ManyToOne. Many properties to One city

Upvotes: 0

Igor
Igor

Reputation: 17

Table name started with a capital letter if tables were created by an ORM middleware (like Hibernate or Entity Framework Core etc.)

SELECT setval('"Table_name_Id_seq"', (SELECT MAX("Id") FROM "Table_name") + 1)
WHERE
    NOT EXISTS (
        SELECT *
        FROM  (SELECT CURRVAL(PG_GET_SERIAL_SEQUENCE('"Table_name"', 'Id')) AS seq, MAX("Id") AS max_id
               FROM "Table_name") AS seq_table
        WHERE seq > max_id
    )

Upvotes: -1

Almat Azhenov
Almat Azhenov

Reputation: 19

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!

Upvotes: 1

SommerEngineering
SommerEngineering

Reputation: 1690

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);

Upvotes: 66

user14161795
user14161795

Reputation: 1

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

Upvotes: -17

rafaelnaskar
rafaelnaskar

Reputation: 783

For future searchs, use ON CONFLICT DO NOTHING.

Upvotes: 4

Keval
Keval

Reputation: 773

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

Upvotes: 6

Stephen Denne
Stephen Denne

Reputation: 37007

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.

Upvotes: 6

stacker
stacker

Reputation: 68962

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.

Upvotes: 1

Related Questions