sql - 带有 WHERE 子句的 PostgreSQL Upsert

Question

我正在尝试将 Oracle 合并查询迁移到 PostgreSql。如 this 中所述文章，Postgres UPSERT 语法支持“where 子句”来识别冲突条件。

不幸的是，该网页没有提供带有“where 子句”的示例。我试着在别处寻找它，但找不到。因此这个问题。

按照上面给定网页中的相同示例，这里是一个示例设置:

CREATE TABLE customers (
    customer_id serial PRIMARY KEY,
    name VARCHAR UNIQUE,
    email VARCHAR NOT NULL,
    active bool NOT NULL DEFAULT TRUE
);

INSERT INTO customers (NAME, email) VALUES
 ('IBM', 'contact@ibm.com'),
 ('Microsoft', 'contact@microsoft.com'),
 ('Intel','contact@intel.com');


SELECT * FROM customers;
 customer_id |   name    |         email         | active
-------------+-----------+-----------------------+--------
           1 | IBM       | contact@ibm.com       | t
           2 | Microsoft | contact@microsoft.com | t
           3 | Intel     | contact@intel.com     | t
(3 rows)

我希望我的 UPSERT 语句看起来像这样:

INSERT INTO customers (NAME, email)
VALUES
('Microsoft', 'hotline@microsoft.com') 
ON CONFLICT where (name = 'Microsoft' and active = TRUE)
DO UPDATE SET email = 'hotline@microsoft.com';

这个例子有点做作，但我希望我能在这里传达要点。

Answer 1

您需要一个部分索引。删除 name 列上的 uniqe 约束并在该列上创建部分索引:

CREATE TABLE customers (
    customer_id serial PRIMARY KEY,
    name VARCHAR,
    email VARCHAR NOT NULL,
    active bool NOT NULL DEFAULT TRUE
);

CREATE UNIQUE INDEX ON customers (name) WHERE active;

INSERT INTO customers (NAME, email) VALUES
 ('IBM', 'contact@ibm.com'),
 ('Microsoft', 'contact@microsoft.com'),
 ('Intel','contact@intel.com');

查询应该如下所示:

INSERT INTO customers (name, email)
VALUES
    ('Microsoft', 'hotline@microsoft.com') 
ON CONFLICT (name) WHERE active
DO UPDATE SET email = excluded.email;

SELECT *
FROM customers;

 customer_id |   name    |         email         | active 
-------------+-----------+-----------------------+--------
           1 | IBM       | contact@ibm.com       | t
           3 | Intel     | contact@intel.com     | t
           2 | Microsoft | hotline@microsoft.com | t
(3 rows)    

注意特殊记录的正确使用 excluded. 根据 the documentation:

The SET and WHERE clauses in ON CONFLICT DO UPDATE have access to the existing row using the table's name (or an alias), and to rows proposed for insertion using the special excluded table.