Re: UPSERT and HOT-update

Daniel,

* CHENG Yuk-Pong, Daniel (j16sdiz(at)gmail(dot)com) wrote:
> I am doing a INSERT...ON CONFLICT...UPDATE.. on a table. The query is
> mostly-UPDATE and does not change any columns most of the time, like
> so:
>
> CREATE INDEX ON book(title);
> INSERT INTO book (isbn, title, author, lastupdate) VALUES ($1,$2,$3, now())
> ON CONFLICT (isbn) DO UPDATE set title=excluded.title,
> author=excluded.author, lastupdate=excluded.lastupdate;
>
> PostgreSQL seems to consider the title as changed and refused to do a
> HOT-update. It works if I remove the `title=...` part.
>
> Are there any tricks to make it smarter? The title don't change most
> of the time after all.

If it's really that infrequent for the title to change, you could do
something like:

insert into book (isbn, title, author, lastupdate) values ('$1','$2',
'$3', now()) on conflict (isbn) do update set author=excluded.author,
lastupdate = excluded.lastupdate where book.title = excluded.title;

and if that doesn't change any rows then the title did change and you
need to run the command you have above.

What might be kind of neat would be to have multiple UPDATE clauses
allowed for the INSERT .. ON CONFLICT DO UPDATE and then you could have
different WHERE clauses and do it all in one command.

Another interesting idea would be a different kind of 'UPDATE SET'
operation (maybe '*=' or something?) which says "only change this if the
value actually changed." There's clearly a lot of cases where that
use-case is desired.

Thanks!

Stephen