IS DISTINCT FROM vs NOT EQUAL for non-null columns in Postgres?

Question

I'm defining a trigger similar to the code posted in this answer.

CREATE TRIGGER trigger_update_account_details
AFTER UPDATE ON account_details
FOR EACH ROW
WHEN (OLD.email    IS DISTINCT FROM NEW.email
   OR OLD.username IS DISTINCT FROM NEW.username
   OR OLD.password IS DISTINCT FROM NEW.password) 
EXECUTE FUNCTION notify_insert_account_details();

However, let's say that email, username, and password are not NULLABLE columns.

It's my understanding that you use IS DISTINCT FROM when a column is nullable, but in my case, all of these values are NOT NULL.

In this case, is it still correct to use IS DISTINCT FROM, or should it simply be something like this instead?

WHEN (OLD.email    != NEW.email
   OR OLD.username != NEW.username
   OR OLD.password != NEW.password)

I'd imagine this latter would not only be more correct, but also faster, because no null check is performed. Is this correct?

From the docs [Comparison operators](https://www.postgresql.org/docs/current/functions-comparison.html): *For non-null inputs, IS DISTINCT FROM is the same as the <> operator. However, if both inputs are null it returns false, and if only one input is null it returns true. Similarly, IS NOT DISTINCT FROM is identical to = for non-null inputs, but it returns true when both inputs are null, and false when only one input is null. Thus, these predicates effectively act as though null were a normal data value, rather than “unknown”.* So probably no difference. Test to confirm. — Adrian Klaver, Apr 27 '23 at 15:10

score 2 · Accepted Answer · answered Apr 27 '23 at 15:07

You are correct that using IS DISTINCT FROM is not necessary for non-nullable columns, and using the != operator instead would be more appropriate and likely faster since no null check is needed.

Therefore, you can use the following code for your trigger:

CREATE TRIGGER trigger_update_account_details
AFTER UPDATE ON account_details
FOR EACH ROW
WHEN (OLD.email != NEW.email
OR OLD.username != NEW.username
OR OLD.password != NEW.password)
EXECUTE FUNCTION notify_insert_account_details();

This will ensure that the trigger only fires when the email, username, or password values are actually updated.

`IS DISTINCT FROM` is a generalization of `<>`. In this particular case (all columns are non-nullable) they behave in the same way. — The Impaler, Apr 27 '23 at 16:50

IS DISTINCT FROM vs NOT EQUAL for non-null columns in Postgres?

1 Answers1