Postgresql – Compare a string with a null value

I’m a bit puzzled because I believe the answer to this question is fairly simple but I’ve searched and tried several options and couldn’t find the right answer.

The database is a PostgreSQL 13.1

I am using an API which sends a JSON object to a stored function in the database as follows:

select * from api.car_model_create( '{
  "payload": {
    "manufacturer": "ai3ZV7PzbP5dNo2fb9q9QGjj2nS5aWJm",
    "name": "SR22",
    "variant": "G2",
    "subname": null
},
    "stk": "YbtmjypXMqXb1U5WOq53DxkaxrbIxl4X"
}'::json
);

​x
 
select * from api.car_model_create( '{  "payload": {    "manufacturer": "ai3ZV7PzbP5dNo2fb9q9QGjj2nS5aWJm",    "name": "SR22",    "variant": "G2",    "subname": null},    "stk": "YbtmjypXMqXb1U5WOq53DxkaxrbIxl4X"}'::json);​

The function queries a table with the following structure:

CREATE TABLE app.car_models (
   id INTEGER NOT NULL
   , public_id CHARACTER(32) DEFAULT (api.random_string(32))
   , name CHARACTER VARYING(64) NOT NULL
   , variant CHARACTER VARYING(64)
   , subname CHARACTER VARYING(64)
   , designator CHARACTER VARYING(16)
   , manufacturer INTEGER NOT NULL
   , car_type INTEGER 
   , status INTEGER NOT NULL DEFAULT 1
) WITHOUT OIDS TABLESPACE app;

 
CREATE TABLE app.car_models (   id INTEGER NOT NULL   , public_id CHARACTER(32) DEFAULT (api.random_string(32))   , name CHARACTER VARYING(64) NOT NULL   , variant CHARACTER VARYING(64)   , subname CHARACTER VARYING(64)   , designator CHARACTER VARYING(16)   , manufacturer INTEGER NOT NULL   , car_type INTEGER    , status INTEGER NOT NULL DEFAULT 1) WITHOUT OIDS TABLESPACE app;​

Inside the function is a query like this:

SELECT count(*)
FROM app.car_models am, app.business_entities be
WHERE am.manufacturer=be.id
AND be.public_id=$1
AND lower(am.name) = lower($2)
AND lower(am.variant) = lower($3)
AND lower(am.subname) = lower($4);

 
SELECT count(*)FROM app.car_models am, app.business_entities beWHERE am.manufacturer=be.idAND be.public_id=$1AND lower(am.name) = lower($2)AND lower(am.variant) = lower($3)AND lower(am.subname) = lower($4);​

Everything works as expected until one of the values of “variant” or “subname” is passed as NULL. These two are the $3 and $4 in the query. The table accepts null values for these two columns. If the value of “variant” or “subname” passed by the JSON object is null the query doesn’t return any result even if the row exists in the table. I must be missing something really simple or basic. But I can’t find it.

EDIT TO ADD A MINIMUM REPRODUCIBLE EXAMPLE:

CREATE TABLE car_models (
   id INTEGER NOT NULL
   , name CHARACTER VARYING(64) NOT NULL
   , variant CHARACTER VARYING(64)
   , subname CHARACTER VARYING(64)
);


INSERT INTO car_models VALUES (1, 'Name 1', 'Variant 1', 'Subname 1');
INSERT INTO car_models VALUES (2, 'Name 2', 'Variant 2', 'Subname 2');
INSERT INTO car_models VALUES (3, 'Name 3', NULL, 'Subname 3');
INSERT INTO car_models VALUES (4, 'Name 4', 'Variant 4', NULL);


SELECT count(*)
FROM car_models
WHERE lower(name) = lower('Name 4')
AND lower(variant) = lower('Variant 4')
AND lower(subname) = lower(null);

 
CREATE TABLE car_models (   id INTEGER NOT NULL   , name CHARACTER VARYING(64) NOT NULL   , variant CHARACTER VARYING(64)   , subname CHARACTER VARYING(64));​​INSERT INTO car_models VALUES (1, 'Name 1', 'Variant 1', 'Subname 1');INSERT INTO car_models VALUES (2, 'Name 2', 'Variant 2', 'Subname 2');INSERT INTO car_models VALUES (3, 'Name 3', NULL, 'Subname 3');INSERT INTO car_models VALUES (4, 'Name 4', 'Variant 4', NULL);​​SELECT count(*)FROM car_modelsWHERE lower(name) = lower('Name 4')AND lower(variant) = lower('Variant 4')AND lower(subname) = lower(null);​

Answer

Postgres supports standard null-safe equality operator is distinct from, which does exactly what you ask for:

SELECT count(*)
FROM car_models
WHERE lower(name)    IS NOT DISTINCT FROM lower('Name 4')
  AND lower(variant) IS NOT DISTINCT FROM lower('Variant 4')
  AND lower(subname) IS NOT DISTINCT FROM lower(null);

 
SELECT count(*)FROM car_modelsWHERE lower(name)    IS NOT DISTINCT FROM lower('Name 4')  AND lower(variant) IS NOT DISTINCT FROM lower('Variant 4')  AND lower(subname) IS NOT DISTINCT FROM lower(null);​

Demo on DB Fiddle:

| count |
| ----: |
|     1 |

Side note: do you really need lower() here? It is not obvious from your sample data. Note that using this function prevents the database from taking advantage of an index (unless you do create an index on this specific expression).

Advertisement

Answer