UPSERT and HOT-update

From: "CHENG Yuk-Pong, Daniel " <j16sdiz(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: UPSERT and HOT-update
Date: 2016-03-16 08:53:36
Message-ID: CAOcLnUcsSw+G30kyc5UurDoZWh9yqMb+1aO6ixQXh0yEN8RTzg@mail.gmail.com
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi List,

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.

Regards,
Daniel

Responses

Browse pgsql-general by date

  From Date Subject
Next Message otheus uibk 2016-03-16 08:59:59 Re: How to Qualifying or quantify risk of loss in asynchronous replication
Previous Message pbj 2016-03-15 23:39:48 Re: MongoDB 3.2 beating Postgres 9.5.1?