从Postgres9.4到Greenplum的数据迁移过程中，应如何处理UNIQUE约束

问题描述

当我在greenplum中执行以下sql(由Postgres9.4的pg_dump生成的sql文件包含)时，

when I execute the following sql (which is contained by a sql file generated by pg_dump of Postgres9.4) in greenplum:

CREATE TABLE "public"."trm_concept" (
"pid" int8 NOT NULL,
"code" varchar(100)  NOT NULL,
"codesystem_pid" int8,
"display" varchar(400) ,
"index_status" int8,
CONSTRAINT "trm_concept_pkey" PRIMARY KEY ("pid"),
CONSTRAINT "idx_concept_cs_code" UNIQUE ("codesystem_pid", "code")
);

我收到此错误:

ERROR:  Greenplum Database does not allow having both PRIMARY KEY and UNIQUE constraints

为什么greenplum不允许这样做?我真的需要这个独特的约束来保证某些规则，如何在greenplum中修复它?

why greenplum doesn't allow this? I really need this unique constraint to guarantee some rule, how can I fix it in greenplum?

推荐答案

• UNIQUE约束是通过btree索引完成的
• 主键暗示UNIQUE和NOT NULL
• GreenPlum分发给子/碎片或任何您声称为UNIQUE的物品.
• a UNIQUE constraint is done with a btree index
• Primary Keys imply UNIQUE and NOT NULL
• GreenPlum distributes to child/shards or whatever on whatever you claim as UNIQUE.

要让GreenTree实施UNIQUE约束-根据需要-该索引必须为

For GreenTree to implement a UNIQUE constraint -- as you want -- that index would have to be

• 复制给每个孩子
• 以符合ACID的方式更新

这样做将完全消除运行GreenPlum的好处.您不妨回到PostgreSQL.

Doing that would totally remove the benefits of running GreenPlum. You may as well move back to PostgreSQL.

有关CREATE TABLE的文档

同一位医生这样说，关于PRIMARY KEY，

Same doc says this about PRIMARY KEY,

这是创建索引

这篇关于从Postgres9.4到Greenplum的数据迁移过程中，应如何处理UNIQUE约束的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！