带有WHERE子句的PostgreSQL Upsert [英] PostgreSQL Upsert with a WHERE clause
问题描述
我正在尝试将Oracle合并查询迁移到PostgreSql。如本文所述，Postgres UPSERT语法支持 where子句来标识以下条件
I am trying to migrate an Oracle merge query to PostgreSql. As described in this article, Postgres UPSERT syntax supports a "where clause" to identify conditions of conflict.
不幸的是，该网页没有提供带有 where子句的示例。我尝试在其他地方搜索它，但找不到它。因此就是这个问题。
Unfortunately, that webpage does not provide an example with the "where clause". I tried searching for it elsewhere but could not find it. Hence this question.
按照上面给出的网页中的相同示例，这里是一个示例设置：
Following the same example in the above given webpage, here is an example setup:
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语句看起来像这样：
I want my UPSERT statement to look something like this:
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';
该示例有些虚构，但我希望能够在这里传达要点。
The example is a bit contrived but I hope I have been able to communicate the gist here.
推荐答案
您需要部分索引。在列 name 上删除uniqe约束，并在列上创建部分索引：
You need a partial index. Drop uniqe constraint on the column name and create a partial index on the column:
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)
请注意特殊记录<$ c $的正确使用c>排除。每个文档：
ON CONFLICT DO UPDATE中的SET和WHERE子句可以使用表名（或别名）访问现有行，并建议使用特殊的 excluded 表插入的行。
这篇关于带有WHERE子句的PostgreSQL Upsert的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
