Re: INSERT ... ON CONFLICT {UPDATE | IGNORE}

On Thu, Oct 9, 2014 at 11:11 AM, Peter Geoghegan <pg(at)heroku(dot)com> wrote:
> On Thu, Oct 9, 2014 at 12:38 AM, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:
>> Do not use CONFLICTING() which looks like it is a function.
>
> So is ROW(). Or COALESCE().

ROW and COALESCE behave almost like functions: they operate on any
expression or value you pass to them.

db=# select coalesce('bar');
coalesce
----------
bar

Not so with CONFLICTING(), it only accepts a column name -- not a
value -- and has knowledge of the surrounding statement that ordinary
function-like constructs don't.

db=# INSERT into evt_type (name) values ('foo') on conflict UPDATE set
name=conflicting('bar');
ERROR: syntax error at or near "'bar'"
LINE 1: ...lues ('foo') on conflict UPDATE set name=conflicting('bar');

> If you don't have a word that you think would more clearly indicate
> the intent of the expression, I'm happy to hear suggestions from
> others.

I also like NEW due to similarity with triggers, but I see your
concern about it not actually being "new".

Regards,
Marti