postgresql: error duplicate key value violates unique constraint
As the error message tells you - you can not have two rows with the same value in the columns dementia_type, snapid
since they need to be unique.
You have to make sure that the two databases has the same values for dementia_type, snapid
.
A workaround would be to add a column to your table alter table t_types_of_dementia add column id serial generated always
and use that as primary key instead of your current.
Comments
-
Shehroz over 3 years
This question have been asked by several people but my problem seems to be different.
Actually I have to merge same structured tables from different databases in postgresql into a new DB. What I am doing is that I connect to remote db using dblink, reads that table in that db and insert it into the table in the current DB like belowINSERT INTO t_types_of_dementia SELECT * FROM dblink('host=localhost port=5432 dbname=snap-cadence password=xxxxxx', 'SELECT dementia_type, snapid FROM t_types_of_dementia') as x(dementia_type VARCHAR(256),snapid integer);
First time this query runs fine, but when I run it again or try to run it with some other remote database's table: it gives me this error
ERROR: duplicate key value violates unique constraint "t_types_of_dementia_pkey"
I want that this new tables gets populated by entries of others tables from other dbs. Some of the solutions proposed talks about sequence, but i am not using any
The structure of the table in current db is
CREATE TABLE t_types_of_dementia( dementia_type VARCHAR(256), snapid integer NOT NULL, PRIMARY KEY (dementia_type,snapid) );
P.S. There is a specific reason why both the columns are used as a primary key which may not be relevant in this discussion, because same issue happens in other tables where this is not the case.
-
Shehroz almost 13 yearsThanks @Jonas. Would there be a work around to relax this constraint?
-
Jonas almost 13 years@Shehroz: You need to use another primary key, see my update.
-
Shehroz almost 13 years@Jonas-Thanks for your pointer. In other tables the case might be different, but I get your central idea.