Django: duplicate key value violates unique constraint with Postgresql not with SQlite -> why?

Question

I have just deploy my project with Postgresql database and get an error unique contraint violates I did not have with my sqlite development database

IntegrityError at /randomization_settings/edit/ duplicate key value violates unique constraint "adm_bra_pkey" DETAIL: Key (bra_ide)=(1) already exists.

I have a table (adm_bra) prepopulate with 2 lines.

I try to INSERT new lines with code using models.objects.create() methods in a view.

Why this happen with postgresql and not with sqlite database? I try to DELETE and RESTART SEQUENCE of adm_bra but it doesn't works!

thanks for help

EDIT I have made some tests first I reinitialized my table

DELETE FROM adm_bra;
ALTER SEQUENCE adm_pro_pro_ide_seq RESTART WITH 1;
INSERT INTO adm_bra ("bra_ide","ran_st1","ran_st2","bra_00A_act","bra_00A_lib","bra_00B_act","bra_00B_lib","bra_00C_act","bra_00C_lib","bra_00D_act","bra_00D_lib","bra_log","bra_dat","pay_ide_id") 
VALUES (1,1,1,1,'HCQ+LPV/r',1,'HCQ+DRV/r',1,'LPV/r+TMS',1,'LPV/r+ATS','admin',now(),1);
INSERT INTO adm_bra ("bra_ide","ran_st1","ran_st2","bra_00A_act","bra_00A_lib","bra_00B_act","bra_00B_lib","bra_00C_act","bra_00C_lib","bra_00D_act","bra_00D_lib","bra_log","bra_dat","pay_ide_id") 
VALUES (2,2,1,1,'HCQ+LPV/r',1,'HCQ+DRV/r',1,'LPV/r+TMS',1,'LPV/r+ATS','admin',now(),1);

I get the unique constraint error if I try to execute this code:

Bras.objects.create(
                ran_st1 = 1, 
                ran_st2 = 1,
                bra_00A_act = 1, 
                bra_00A_lib = 'HCQ+LPV/r', 
                bra_00B_act = 1, 
                bra_00B_lib = 'HCQ+DRV/r', 
                bra_00C_act = 1, 
                bra_00C_lib = 'LPV/r+TMS', 
                bra_00D_act = None, 
                bra_00D_lib = None, 
                bra_00E_act = None, 
                bra_00E_lib = None, 
                bra_00F_act = None, 
                bra_00F_lib = None, 
                bra_00G_act = None, 
                bra_00G_lib = None, 
                bra_00H_act = None, 
                bra_00H_lib = None, 
                bra_00I_act = None, 
                bra_00I_lib = None, 
                bra_00J_act = None, 
                bra_00J_lib = None, 
                bra_dat = timezone.now(), 
                bra_log = 'admin', 
                pay_ide_id = 2
            )

But, if execute this code, it works:

b= Bras(           ran_st1 = 1,ran_st2 = 1,
                bra_00A_act = 1,
                bra_00A_lib = 'HCQ+LPV/r', 
                bra_00B_act = 1,
                bra_00B_lib = 'HCQ+DRV/r', 
                bra_00C_act = 1, 
                bra_00C_lib = 'LPV/r+TMS', 
                bra_00D_act = None, 
                bra_00D_lib = None, 
                bra_00E_act = None, 
                bra_00E_lib = None, 
                bra_00F_act = None, 
                bra_00F_lib = None, 
                bra_00G_act = None, 
                bra_00G_lib = None, 
                bra_00H_act = None, 
                bra_00H_lib = None, 
                bra_00I_act = None, 
                bra_00I_lib = None, 
                bra_00J_act = None, 
                bra_00J_lib = None, 
                bra_dat = timezone.now(), 
                bra_log = 'admin', 
                pay_ide_id = 2
            )
b.save()
Bras.objects.create(
                ran_st1 = 1, 
                ran_st2 = 1,
                bra_00A_act = 1, 
                bra_00A_lib = 'HCQ+LPV/r', 
                bra_00B_act = 1, 
                bra_00B_lib = 'HCQ+DRV/r', 
                bra_00C_act = 1, 
                bra_00C_lib = 'LPV/r+TMS', 
                bra_00D_act = None, 
                bra_00D_lib = None, 
                bra_00E_act = None, 
                bra_00E_lib = None, 
                bra_00F_act = None, 
                bra_00F_lib = None, 
                bra_00G_act = None, 
                bra_00G_lib = None, 
                bra_00H_act = None, 
                bra_00H_lib = None, 
                bra_00I_act = None, 
                bra_00I_lib = None, 
                bra_00J_act = None, 
                bra_00J_lib = None, 
                bra_dat = timezone.now(), 
                bra_log = 'admin', 
                pay_ide_id = 2
            )

Arvind Kumar · Accepted Answer · 2020-05-27 16:47:05Z

1

Actually You are restarting sequence with 1 and then in your query you are passing the value of bra_ide manually. So, when you execute those queries, the sequence is not used to create the default column. When you try to insert, the sequence provides a value of 1, as it is being run for first time and you get an Integrity Error.

While running queries remove the bra_ide column and its values. So your sequence will generate the values automatically.

answered May 27, 2020 at 16:47

Arvind Kumar

98312 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Arvind Kumar Over a year ago

Glad to help. Mark it as the answer to help others.

Collectives™ on Stack Overflow

Django: duplicate key value violates unique constraint with Postgresql not with SQlite -> why?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related