PostgreSQL含两个唯一索引的表upsert操作可行性咨询

问题A解答：不能直接执行 #

你写的语句存在两个问题：

1. 插入值里的'A'、'B'是字符串类型，但表结构中base和quote字段为integer类型，直接执行会报类型不匹配错误。
2. 你的pairs_dup_not_null_idx是带WHERE delivery_date IS NOT NULL过滤条件的部分唯一索引，ON CONFLICT子句如果要命中这类索引，必须明确加和索引完全等价的过滤条件，你当前语句的ON CONFLICT没有加对应过滤条件，PostgreSQL无法匹配到该索引，会直接抛出唯一约束冲突报错，不会触发DO UPDATE逻辑。

符合要求的正确写法如下：

INSERT INTO pairs_dup(base, quote, delivery_date, data) values (1, 2, '2016-06-22 19:10:25-07', 0.5)
ON CONFLICT (base, quote, delivery_date) 
WHERE delivery_date IS NOT NULL
DO UPDATE SET data = EXCLUDED.data;

这里用EXCLUDED.data可以直接取到本次插入的新值，不需要硬编码赋值，通用性更强。

问题B解答：不能直接执行 #

你写的语句同样无法匹配到对应部分唯一索引：
你的pairs_dup_null_idx索引仅包含base、quote两列，且过滤条件为delivery_date IS NULL，你当前ON CONFLICT子句列写了三个字段，也没有加对应过滤条件，既匹配不到delivery_date为空的索引，也匹配不到非空的索引，会直接抛出冲突报错。

符合要求的正确写法如下：

INSERT INTO pairs_dup(base, quote, data) values (1, 2, 0.5)
ON CONFLICT (base, quote) 
WHERE delivery_date IS NULL
DO UPDATE SET data = EXCLUDED.data;

通用场景兼容方案 #

目前PostgreSQL原生不支持单条ON CONFLICT同时匹配两个不同的部分唯一索引，如果你需要写一个同时兼容delivery_date为空/非空的upsert语句，可以把两个部分唯一索引替换为一个表达式唯一索引：

-- 先删除原有两个唯一索引
DROP INDEX pairs_dup_not_null_idx;
DROP INDEX pairs_dup_null_idx;
-- 新建统一表达式唯一索引，注意选择的默认时间要和业务无冲突
CREATE UNIQUE INDEX pairs_dup_unified_idx ON public.pairs_dup 
USING btree (base, quote, COALESCE(delivery_date, '1970-01-01 00:00:00+00'::timestamp with time zone));

之后就可以用统一的upsert语句处理两种场景：

-- delivery_date非空场景
INSERT INTO pairs_dup(base, quote, delivery_date, data) values (1, 2, '2016-06-22 19:10:25-07', 0.5)
ON CONFLICT (base, quote, COALESCE(delivery_date, '1970-01-01 00:00:00+00'::timestamp with time zone))
DO UPDATE SET data = EXCLUDED.data;

-- delivery_date为空场景
INSERT INTO pairs_dup(base, quote, data) values (1, 2, 0.5)
ON CONFLICT (base, quote, COALESCE(delivery_date, '1970-01-01 00:00:00+00'::timestamp with time zone))
DO UPDATE SET data = EXCLUDED.data;

内容的提问来源于stack exchange，提问作者sev