PostgreSQL：错误重复键值违反唯一约束 [英] postgresql: error duplicate key value violates unique constraint

问题描述

这个问题已经有好几个人问过，但是我的问题似乎有所不同。

实际上，我必须将Postgresql中来自不同数据库的相同结构化表合并到一个新的DB中。我正在做的是使用dblink连接到远程数据库，读取该数据库中的表并将其插入到当前数据库的表中，如下所示：

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

错误：重复的键值违反了唯一约束
t_types_of_dementia_pkey

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。将这两个列都用作主键是一个特定的原因，在本次讨论中可能并不重要，因为在其他情况下（并非如此），在其他表中也会发生相同的问题。

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.

推荐答案

错误消息告诉您-在 dementia_type，snapid 列中不能有两行具有相同值它们必须是唯一的。

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.

您必须确保两个数据库对于 dementia_type，snapid 。

You have to make sure that the two databases has the same values for dementia_type, snapid.

一种解决方法是在表中添加列更改表t_types_of_dementia添加始终生成的列ID序列并将其用作主键而不是当前键。

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.

这篇关于PostgreSQL：错误重复键值违反唯一约束的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！