Does Postgresql create a new tuple for an no-change update statement? I need to fix some data issues in a Postgresql database, the primary of which is trimming whitespace from text columns. I'm doing this using a statement like:
UPDATE app.products
SET &quot;description&quot; = TRIM(BOTH FROM &quot;description&quot;)

Issuing this on a test database returns UPDATE 2000. The test table contains 2000 rows of which only 1 actually would change with whitespace trimming. Is this resulting in Postgres creating new, unnecessary tuples? Would there be a benefit to adding a WHERE clause? Most of the target columns aren't indexed.
UPDATE app.products
SET &quot;description&quot; = TRIM(BOTH FROM &quot;description&quot;)
WHERE &quot;description&quot; LIKE ' %' OR &quot;description&quot; LIKE '% '
Yes, this will modify all rows, even if the value doesn't change.
If you want to avoid that, either use a WHERE condition:
UPDATE app.products
SET &quot;description&quot; = TRIM(BOTH FROM &quot;description&quot;)
WHERE &quot;description&quot; IS DISTINCT FROM TRIM(BOTH FROM &quot;description&quot;);

or use the suppress_redundant_updates_trigger() trigger function to define a BEFORE UPDATE trigger on the table.
Avoiding the unnecessary updates will improve the performance and reduce table bloat.