PostgreSQL中如何实现部分主键？

有个开发的同事问我，如何实现以下一个场景：test表有3列col1、col2和status，当status=1时col1和col2联合要唯一，当不为status!=1时，col1和col2联合可以不唯一，他需要建一个这样的所谓部分主键，直接建会报如下错误：

postgres=# CREATE TABLE test(co11 VARCHAR(32) NOT NULL,col2 VARCHAR(32) NOT NULL,status SMALLINT);
CREATE TABLE
postgres=# ALTER TABLE test ADD CONSTRAINT pk_test PRIMARY KEY (co11, col2, 1);
ERROR:  syntax error at or near "1"
LINE 1: ...BLE test ADD CONSTRAINT pk_test PRIMARY KEY (co11, col2, 1);
                                                                    ^
复制

直接使用是不行的，会报语法错误，当然也不支持这种常量建主键，其实PG中的主键实现方式就是唯一索引+非空约束，并且PG中还支持部分索引，那么可以使用唯一索引+非空约束+部分索引来实现此需求，代码如下：

postgres=# CREATE UNIQUE INDEX idx_un_col1_col2_test ON test(co11,col2) WHERE status = 1;
CREATE INDEX
复制

测试：

postgres=# CREATE UNIQUE INDEX idx_un_col1_col2_test ON test(co11,col2) WHERE status = 1;
CREATE INDEX
postgres=# INSERT INTO test VALUES('1','1',1);
INSERT 0 1
postgres=# INSERT INTO test VALUES('1','1',2);
INSERT 0 1
postgres=# INSERT INTO test VALUES('1','2',1);
INSERT 0 1
postgres=# INSERT INTO test VALUES('1','2',1);
ERROR:  duplicate key value violates unique constraint "idx_un_col1_col2_test"
DETAIL:  Key (co11, col2)=(1, 2) already exists.
postgres=# INSERT INTO test VALUES('1','1',2);
INSERT 0 1
postgres=# INSERT INTO test VALUES('1','1',1);
ERROR:  duplicate key value violates unique constraint "idx_un_col1_col2_test"
DETAIL:  Key (co11, col2)=(1, 1) already exists.
postgres=# SELECT * FROM test;
 co11 | col2 | status 
------+------+--------
 1    | 1    |      1
 1    | 1    |      2
 1    | 2    |      1
 1    | 1    |      2
(4 rows)
复制

从以上可以看到满足业务需要，如果需要批量插入时，忽略冲突数据，非冲突数据能正常插入，可以使用冲突忽略语法（PG9.5新增功能），如下：

postgres=# INSERT INTO test VALUES('1','1',1),('1','1',3) ON CONFLICT DO NOTHING;
INSERT 0 1
postgres=# SELECT * FROM test;
 co11 | col2 | status 
------+------+--------
 1    | 1    |      1
 1    | 1    |      2
 1    | 2    |      1
 1    | 1    |      2
 1    | 1    |      3
(5 rows)
复制

如果需要在冲突的行进行值更新，功能类似Oracle的merge into，使用如下方式实现：

postgres=# CREATE TABLE test1(co11 VARCHAR(32) NOT NULL,col2 VARCHAR(32) NOT NULL,status SMALLINT,name VARCHAR(64));
CREATE TABLE
postgres=# CREATE UNIQUE INDEX idx_un_col1_col2_test1 ON test1(co11,col2) WHERE status = 1;
CREATE INDEX                                    
postgres=# INSERT INTO test1 VALUES('1','1',1,'AA');
INSERT 0 1                                                                                            
postgres=# INSERT INTO test1 VALUES('1','1',1,'bb'),('1','1',3,'aa') ON CONFLICT (co11,col2) WHERE status = 1 DO UPDATE SET name = EXCLUDED.name;
INSERT 0 2
postgres=# SELECT * FROM test1;
 co11 | col2 | status | name 
------+------+--------+------
 1    | 1    |      1 | bb
 1    | 1    |      3 | aa
(2 rows)
复制

INSERT语法