Use IS NULL and IS NOT NULL instead of direct NULL comparisons in postgreSQL

user_2919544 0
Viewed 133

I have this sql query:

ALTER TABLE outfit
    ADD CONSTRAINT check_non_empty_string CHECK (image_uri <> '');

This works good but SonarCloud raised this as a bug. I am using PostgreSQL 13.2, is there a better way of writing this query or can I safely ignore this error in SonarCloud?

Use IS NULL and IS NOT NULL instead of direct NULL comparisons.

In a Zen-like manner, "NULL" is never equal to anything, even itself. Therefore comparisons using equality operators will always return False, even when the value actually IS NULL.

For that reason, comparison operators should never be used to make comparisons with NULL; IS NULL and IS NOT NULL should be used instead. This extends as well to empty string (""), which is equivalent to NULL for some database engines.

sonarcloud sonarqube postgresql
1 Answers

The check constraint is good as it is, if you want to allow NULL values. Note that a check constraint passes if the result of the expression is NULL.

If you want to forbid NULL values as well, you should additionally do this:

ALTER TABLE outfit ALTER image_uri SET NOT NULL;
user_64643080
Related