1

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.

Anita
  • 2,741
  • 27
  • 28

1 Answers1

0

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;
Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
  • Hi Laurenz - again :) This was the original column definition `image_uri text NOT NULL`, The issue is that we had empty strings inserted and we wanted to make sure both values - empty string and null would not be allowed. – Anita Dec 09 '21 at 17:15
  • Then your definition is good as it is. – Laurenz Albe Dec 09 '21 at 17:17
  • 3
    Since Postgres does not consider the empty string '' to be the same as NULL you need both `NOT NULL` and the chcck constraint.. – Belayer Dec 09 '21 at 17:20
  • Thank you, I will just ignore Sonar warning in that case. – Anita Dec 09 '21 at 17:23