PostgreSQL Upsert with a WHERE clause

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');

The query should look like this:

INSERT INTO customers (name, email)VALUES    ('Microsoft', 'hotline@microsoft.com') ON CONFLICT (name) WHERE activeDO 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)    

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.