disable duplicate key constraint for postgres migration

Question

I'm trying to migrate some data from one postgres rds to another postgres rds but I keep running into ERROR: duplicate key value violates unique constraint "abcd_xyz". I've updated the session_replication_role parameter group value to replica but still can't seem to get past that constraint (and others that I would run into).

demo data but even when I set set session_replication_role to 'replica';...I still get the same error

user ~/Git/go-dlp  $ psql -h blah.us-east-1.rds.amazonaws.com -U user1 -d db1 
Password for user user1: 
psql (13.2, server 11.6)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type "help" for help.

db1=> 
db1=> 
db1=> set session_replication_role to 'replica';
SET
db1=> UPDATE userinfo_v3 SET firstname = 'Alana', lastname = 'Crooks', mobilephone = '+17368645273', ssn = '666612345', ssnhash = '3895fa77f6d70c8c9401275829c78020' WHERE id = '73838726';
UPDATE 1
db1=> UPDATE userinfo_v3 SET firstname = 'Leann', lastname = 'Crist', mobilephone = '+16970011319', ssn = '666612345', ssnhash = '3895fa77f6d70c8c9401275829c78020' WHERE id = '1470593';
ERROR:  duplicate key value violates unique constraint "abcd_xyz"
DETAIL:  Key (ssnhash)=(3895fa77f6d70c8c9401275829c78020) already exists.

I think I have set all the right parameters but I still can't get past that constraint for some reason

Laurenz Albe · Accepted Answer · 2021-04-08 06:05:26Z

1

You cannot disable a primary key or unique constraint. session_replication_role affects only triggers and foreign key constraints.

Your only option is to drop the constraint and create it again after you are done.

answered Apr 8, 2021 at 6:05

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

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

Comments

louis benagha · Accepted Answer · 2021-04-08 05:22:33Z

0

Did you check for any referencing keys? You might want to query id and March to see if any id is referencing itself if it is then it’s regarded as a duplicate and that throw errors

answered Apr 8, 2021 at 5:22

louis benagha

1231 gold badge2 silver badges11 bronze badges

1 Comment

lightweight Over a year ago

sorry...missed a key part of the error...just edited it in...its the ssnhash that is throwing the dup key error: DETAIL: Key (ssnhash)=(3895fa77f6d70c8c9401275829c78020) already exists.

Collectives™ on Stack Overflow

disable duplicate key constraint for postgres migration

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related