PostgreSQL Upsert with a WHERE clause

Question

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.

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', '[email protected]'),
 ('Microsoft', '[email protected]'),
 ('Intel','[email protected]');


SELECT * FROM customers;
 customer_id |   name    |         email         | active
-------------+-----------+-----------------------+--------
           1 | IBM       | [email protected]       | t
           2 | Microsoft | [email protected] | t
           3 | Intel     | [email protected]     | t
(3 rows)

I want my UPSERT statement to look something like this:

INSERT INTO customers (NAME, email)
VALUES
('Microsoft', '[email protected]') 
ON CONFLICT where (name = 'Microsoft' and active = TRUE)
DO UPDATE SET email = '[email protected]';

The example is a bit contrived but I hope I have been able to communicate the gist here.

Answer 1

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', '[email protected]'),
 ('Microsoft', '[email protected]'),
 ('Intel','[email protected]');

The query should look like this:

INSERT INTO customers (name, email)
VALUES
    ('Microsoft', '[email protected]') 
ON CONFLICT (name) WHERE active
DO UPDATE SET email = excluded.email;

SELECT *
FROM customers;

 customer_id |   name    |         email         | active 
-------------+-----------+-----------------------+--------
           1 | IBM       | [email protected]       | t
           3 | Intel     | [email protected]     | t
           2 | Microsoft | [email protected] | t
(3 rows)    

Note the proper use of the special record excluded. Per 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.