How to do multiple columns update on different where condition using PostgreSQL Upsert Using INSERT ON CONFLICT statement

Question

Suppose I have a table like this

create schema test;
CREATE TABLE test.customers (
customer_id serial PRIMARY KEY,
name VARCHAR UNIQUE,
email VARCHAR NOT NULL,
active bool NOT NULL DEFAULT TRUE,
is_active_datetime                   TIMESTAMP(3) NOT NULL DEFAULT'1900-01-01T00:00:00.000Z'::timestamp(3)
updated_datetime                     TIMESTAMP(3) NOT NULL DEFAULT '1900-01-01T00:00:00.000Z'::timestamp(3),
);

Now If i want to update email on conflict name

WHERE $tableName.updated_datetime < excluded.updated_datetime

and i want to update is_active_datetime on conflict name but that condition for this update is where active flag has changed.

WHERE customer.active != excluded.active

basically want to track when active status is changed. so can I do that in single statement like this

Initial insert :

insert  INTO test.customers (NAME, email)
VALUES
('IBM', '[email protected]'),
(
'Microsoft',
'[email protected]'
 ),
(
 'Intel',
 '[email protected]'
);

To achieve my purpose I am trying something like this :

select * from test.customers;

INSERT INTO customers (name, email)
VALUES
(
'Microsoft',
'[email protected]'
)
ON CONFLICT (name)
DO
UPDATE
SET customers.email = EXCLUDED.email
WHERE $tableName.updated_datetime < excluded.updated_datetime
on CONFLICT (name)
do
update
set is_active_datetime = current_timestamp()
WHERE customer.active != excluded.active ;

Is it possible to do this ? How to do this using this method.

These 2 where conditions are diff. so can not combine them with AND OR — PriyalChaudhari
– PriyalChaudhari, Commented Feb 1, 2019 at 23:54

Kaushik Nayak · Accepted Answer · 2019-02-02 05:37:10Z

4

You could update multiple columns with CASE conditions in a single DO UPDATE clause.

INSERT INTO customers  (
    name
    ,email
    ,updated_datetime
    )
VALUES (
    'Microsoft'
    ,'[email protected]'
    ,now()
    ) ON CONFLICT(name) DO

UPDATE
SET email = CASE 
        WHEN customers.updated_datetime < excluded.updated_datetime
            THEN excluded.email
        ELSE customers.email --default when condition not satisfied
        END
    ,is_active_datetime = CASE 
        WHEN customers.active != excluded.active
            THEN current_timestamp
        ELSE customers.is_active_datetime
    END;

Demo

answered Feb 2, 2019 at 5:37

Kaushik Nayak

32k6 gold badges36 silver badges54 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to do multiple columns update on different where condition using PostgreSQL Upsert Using INSERT ON CONFLICT statement

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related