Duplicate key value violates unique constraint Postgresql table after backup

I have come across a problem a number of times that a table no longer works after you put a backup. You can't add any record at all. Your table sequence might be out of sync and that you have to manually bring it back in sync.


duplicate key value violates unique constraint 👀


return self.cursor.execute(sql, params)

django.db.utils.IntegrityError: duplicate key value violates unique constraint "job_statpersource_pkey"

DETAIL: Key (id)=(7199) already exists.




SELECT MAX(the_primary_key) FROM the_table;

SELECT nextval('the_primary_key_sequence');


For Capital letters name of sequence you have to use ""


SELECT nextval('"AccountingPeriod_accountingperiod_id_seq"');

If the first value is higher than the second, your sequence is out of sync. Backup your PG database (just in case), then run the following commands:


SELECT MAX(id) FROM job_statpersource;


Find Sequence in database


SELECT sequence_schema, sequence_name FROM information_schema.sequences ORDER BY sequence_name



\ds


job_statpersource_id_seq


SELECT nextval('job_statpersource_id_seq');






vindazo_nl=> SELECT nextval('job_statpersource_id_seq');

nextval

---------

7200

(1 row)


vindazo_nl=> SELECT MAX(id) FROM job_statpersource;

max

------

7245

(1 row)




So it's clear that something went wrong.. We need to sync it..

And then we need a script that will loop through all the tables automatically and check this way. Is that possible?

SQL Sql with which you can restore operation.

SELECT nextval('"AccountingPeriod_accountingperiod_id_seq"');




SELECT setval('job_statpersource_id_seq', (SELECT MAX(id) FROM job_statpersource)+1);


Ok, this is the solution to fix table seq.


Find more problematic sequentions


You can write output from Sql command in a separate txt file.


SELECT sequence_schema, sequence_name FROM information_schema.sequences ORDER BY sequence_name;






Then you can clean this text in VIM.. :%s/ public | //g


You will get lines with seq names


….

auth_user_id_seq

auth_user_user_permissions_id_seq

city2region_city_id_seq

company_company_employees_id_seq

company_company_id_seq

company_nacebel_id_seq

company_sector_id_seq

company_source_id_seq

contact_me_contactme_id_seq

contactme_contact_msg_id_seq

crawler_offlinetext_id_seq

crawler_page_id_seq

crawler_param_id_seq

Crawler_proxytable_id_seq




In my case this is a file with 150 names. So you can now read it in python or django command run it in


See here for a django code example.


from django.db import connection

cursor.execute(“SELECT nextval('job_statpersource_id_seq');”)

row = cursor.fetchone()

vim job/management/commands/check_seq_sql.py


from django.conf import settings


from django.db import connection

from django.core.management.base import BaseCommand, CommandError


class Command(BaseCommand):

"""

Send messages from que

"""

def handle(self, *args, **options):

"""

"""

lines = open('/home/admin/deployment/nl_vindazo/nl_vindazo/data/seq.txt', 'r').readlines()

for line in lines:

self.print_counts(line)


def print_counts(self, line):

line = line.strip()

print('------------------- ' + line + '-----------------')

with connection.cursor() as cursor:

sql = "SELECT nextval('" + line + "');"

self.print_sql(sql, cursor)

sql = "SELECT MAX(id) FROM " + line.replace('_id_seq', '') + ";"

self.print_sql(sql, cursor)

print("/////////////////////")


def print_sql(self, sql, cursor):

print(sql)

cursor.execute(sql)

row = cursor.fetchone()

print(row)


With this command you can see then output something like this..


------------------- account_emailaddress_id_seq-----------------

SELECT nextval('account_emailaddress_id_seq');

(187592L,)

SELECT MAX(id) FROM account_emailaddress;

(187589,)

/////////////////////

------------------- account_emailconfirmation_id_seq-----------------

SELECT nextval('account_emailconfirmation_id_seq');

(6L,)

SELECT MAX(id) FROM account_emailconfirmation;

(None,)

/////////////////////

------------------- auth_group_id_seq-----------------

SELECT nextval('auth_group_id_seq');

(6L,)

SELECT MAX(id) FROM auth_group;

(None,)

/////////////////////

------------------- auth_group_permissions_id_seq-----------------

SELECT nextval('auth_group_permissions_id_seq');

(6L,)

SELECT MAX(id) FROM auth_group_permissions;

(None,)

/////////////////////

------------------- auth_permission_id_seq-----------------

SELECT nextval('auth_permission_id_seq');

(364L,)

SELECT MAX(id) FROM auth_permission;

(358,)

/////////////////////



So the next interval must be higher than max id.. If not, you will get an error when creating a key.

Comments