postgresql：错误重复键值违反唯一约束
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6895419/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
postgresql: error duplicate key value violates unique constraint
提问by Shehroz
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 below
有几个人问过这个问题，但我的问题似乎有所不同。
实际上，我必须将来自 postgresql 中不同数据库的相同结构化表合并到一个新数据库中。我正在做的是我使用 dblink 连接到远程数据库，读取该数据库中的该表并将其插入到当前数据库中的表中，如下所示
INSERT 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"
错误：重复键值违反唯一约束“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
当前db中表的结构是
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.
PS 将两列用作主键有一个特定的原因，这在本讨论中可能无关紧要，因为在其他表中发生了同样的问题，但情况并非如此。
采纳答案by Jonas
As the error message tells you - you can not have two rows with the same value in the columns dementia_type, snapidsince they need to be unique.
正如错误消息告诉您的那样 - 列中不能有两行具有相同的值，dementia_type, snapid因为它们需要是唯一的。
You have to make sure that the two databases has the same values for dementia_type, snapid.
您必须确保两个数据库具有相同的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 alwaysand use that as primary key instead of your current.
一种解决方法是向表中添加一列alter table t_types_of_dementia add column id serial generated always并将其用作主键而不是当前键。
