CODEWITHSUNDEEP
postgresqlamazon-rds
lightweight
lightweight

Reputation: 3337

disable duplicate key constraint for postgres migration

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

Upvotes: 0

Views: 1246

Answers (2)

Laurenz Albe
Laurenz Albe

Reputation: 247093

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.

Upvotes: 1

louis benagha
louis benagha

Reputation: 115

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

Upvotes: 0

Related Questions