From: | Geoff Winkless <pgsqladmin(at)geoff(dot)dj> |
---|---|
To: | Postgres General <pgsql-general(at)postgresql(dot)org> |
Subject: | testing for DEFAULT insert value in ON CONFLICT DO UPDATE query |
Date: | 2018-02-09 16:31:45 |
Message-ID: | CAEzk6ffVvmTrQJx6Hj7n5dSEXTUut7aqeefTB3NjifzcJ4k2dw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Hi
Is there any way to tell if a conflicting row in an multi-line INSERT
used the DEFAULT directive?
I would like to be able to upsert a bunch of rows and only UPDATE the
conflicting rows where the value set was not new - the way I do this
for NULLable columns is to just write NULL in the INSERT subclause for
the columns that I don't want to set and use
SET column=CASE WHEN EXCLUDED.column IS NULL THEN tablename.column
ELSE EXCLUDED.column END
(or COALESCE(), if you prefer);
however for NOT NULL columns with a default, I don't know how I can do
this. I was hoping for something like an "IS DEFAULT" test but that
didn't work. I can't just test for the default value itself because
there might be times when I want to update the value to the default,
overriding an existing value.
I also can't simply exclude the column from the insert because for
some rows I _will_ be setting the value.
Am I missing something obvious, or am I going to have to change the
column to accept NULLs in order to make this work?
Thanks
Geoff
From | Date | Subject | |
---|---|---|---|
Next Message | Steven Hirsch | 2018-02-09 16:58:23 | Re: Odd behavior with 'currval' |
Previous Message | Francisco Olarte | 2018-02-09 15:47:10 | Re: Odd behavior with 'currval' |