CODEWITHSUNDEEP
postgresqlbooleanpostgresql-10
sojim2
sojim2

Reputation: 1307

How to compare boolean values between tables for update in a clean way

In this fiddle: https://rextester.com/edit/SPVQGW21946

I would like to update tableA with tableB -- the value on the first row, field ccc from null to false

enter image description here

Here's an upsert query that needs a way to compare if tableB has different values, then update those fields in a simple way.

I'm stuck at the where clause, I'm not sure how to compare boolean fields to update:

INSERT INTO tableA (company, option, new, gap, ccc) 
   SELECT company, option, new, gap, ccc
   FROM   tableB
 ON CONFLICT (company, option) 
 DO update
   set new= EXCLUDED.new,
   gap = excluded.gap,
   ccc = excluded.ccc

 WHERE 
      ?

Is there a clean way to compare boolean values to update?

(The reason I have insert is because tableB may have new rows.)

Upvotes: 0

Views: 48

Answers (1)

Kaushik Nayak
Kaushik Nayak

Reputation: 31666

I think you want IS DISTINCT FROM in the where clause.

 WHERE (tableA.new,tableA.gap,tableA.ccc) 
IS DISTINCT FROM (EXCLUDED.new,excluded.gap,excluded.ccc);

Demo

Upvotes: 1

Related Questions