Re: Proposal for UPDATE: do not insert new tuple on heap if update does not change data

On Wed, Jan 20, 2016 at 3:55 AM, Gasper Zejn <zelo(dot)zejn(at)gmail(dot)com> wrote:

> I was wondering if PostgreSQL adds new tuple if data is not changed
> when using UPDATE. It turns out it does add them and I think it might
> be beneficial not to add a new tuple in this case, since it causes a
> great deal of maintenance: updating indexes, vacuuming table and
> index, also heap fragmentation.

If you have one or more tables on which you routinely updated rows
to the values they already have, you might want to attach an update
trigger using the suppress_redundant_updates_trigger() function.

http://www.postgresql.org/docs/current/interactive/functions-trigger.html

A better solution, where possible, is to use the WHERE clause to
avoid the update attempt where the new values are not distinct from
the old ones.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company