postgresql：错误重复键值违反唯一约束
这个问题已经有几个人问过，但我的问题似乎不同。
实际上，我必须将 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);
第一次此查询运行良好，但是当我再次运行它或尝试运行它时与其他一些远程数据库的表：它给了我这个错误
错误：重复的键值违反了唯一约束 “t_types_of_dementia_pkey”
我希望这个新表由来自其他数据库的其他表的条目填充。 提出的一些解决方案讨论了序列，但我没有使用任何
当前数据库中表的结构是
CREATE TABLE t_types_of_dementia(
dementia_type VARCHAR(256),
snapid integer NOT NULL,
PRIMARY KEY (dementia_type,snapid)
);
PS 这两列都用作主键有一个特定的原因，这在本次讨论中可能不相关，因为同样的问题发生在其他表中，但情况并非如此。
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
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"
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.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
正如错误消息告诉您的那样 - 您不能在
dementia_type, snapid列中拥有具有相同值的两行，因为它们需要是唯一的。您必须确保两个数据库的
dementia_type、snapi具有相同的值。解决方法是向表中添加一列
alter table t_types_of_dementia add column id serial generated always并将其用作主键而不是当前的主键。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.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 alwaysand use that as primary key instead of your current.