testing for DEFAULT insert value in ON CONFLICT DO UPDATE query

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

Browse pgsql-general by date

  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'