CODEWITHSUNDEEP
databasepostgresqlsql-updatedatabase-performancepostgresql-performance
OneSneakyMofo
OneSneakyMofo

Reputation: 1289

Does updating a row with the same value actually update the row?

I have a performance-related question. Let's say I have a user with the first name Michael. Take the following query:

UPDATE users
SET first_name = 'Michael'
WHERE users.id = 123

Will the query actually execute the update? If so, how do I prevent it from happening?

Upvotes: 3

Views: 1967

Answers (2)

paparazzo
paparazzo

Reputation: 45096

Yes, it is more efficient to do this 

UPDATE users
SET first_name = 'Michael'
WHERE users.id = 123 
and (first_name <> 'Michael' or first_name is null)

It can make a lot of difference.
Not just the time for the update itself - it does not have to take am update lock.

Upvotes: 4

Pavel Stehule
Pavel Stehule

Reputation: 45855

The UPDATE in PostgreSQL doesn't check a difference between original and new value by default. You can skip useless updates with BEFORE UPDATE trigger:

CREATE OR REPLACE FUNCTION public.update_trigger_skip()
 RETURNS trigger
 LANGUAGE plpgsql
AS $function$
BEGIN
  IF old IS NOT DISTINCT FROM new THEN
    RETURN NULL;
  END IF;
  RETURN new;
END;
$$;

-- run this trigger as first - start name with "_"
CREATE TRIGGER _omega_update_skip BEFORE UPDATE ON omega;
  FOR EACH ROW EXECUTE PROCEDURE update_trigger_skip();

PostgreSQL execute triggers in order based on names of triggers. This solution is simple and generic, and slow (if you use massive updates - can be ok for few updated rows). There is a overhead of trigger execution. If you can, modify your queries, then do it - described by @Frisbee.

Upvotes: 2

Related Questions