PostgreSQL - Comparison operator with character varying

I want to query a PostgreSQL table with comparison operators. This table have two character varying columns.

Table

CREATE TABLE IF NOT EXISTS test.test
(
    scope character varying COLLATE pg_catalog."default",
    project_code character varying COLLATE pg_catalog."default"
)

Values

INSERT INTO test.test(scope, project_code) VALUES (NULL, 'AA');
INSERT INTO test.test(scope, project_code) VALUES ('A', 'AA');

When I wan't to query values with a project_code = 'AA' and a scope = 'A', I write:

SELECT * FROM test.test WHERE project_code LIKE 'AA' AND scope LIKE 'A';

It returns me one row, result is ok.

But when I try to query values with a project_code = 'AA' and scope with any other values than 'A', I write:

SELECT * FROM test.test WHERE project_code LIKE 'AA' AND scope NOT LIKE 'A';

It doesn't return me any results. But I have a row who match this. How to explain this and how to write this query ?

I try other comparaison operators <> and !=, same result. I'm using PostgreSQL 13.6.

CodePudding user response：

You need to use a NULL safe comparison operator. The SQL standard defines the is not distinct from operator as the NULL safe version of <> and Postgres supports this:

SELECT * 
FROM test.test 
WHERE project_code = 'AA' 
  AND scope IS DISTINCT FROM 'A';

CodePudding user response：

NULL in most operations will return NULL. For example

SELECT NULL LIKE 'A', NULL NOT LIKE 'A'

returns (NULL, NULL). Probably handling the NULL case specifically helps:

SELECT
    * 
FROM
    test.test 
WHERE
         project_code LIKE 'AA'
    AND  (scope IS NULL OR scope NOT LIKE 'A')

The solution offered by @a_horse_with_no_name is more elegant; this solution may be interesting when using "wildcards" in the LIKE operator.

CodePudding user response：

select null like 'a' is true; --return false
select null not like 'a' is true; --return false 
select null like 'a'; --return null
select null not like 'a' ; --return null

https://www.postgresql.org/docs/current/functions-matching.html.

If pattern does not contain percent signs or underscores, then the pattern only represents the string itself; in that case LIKE acts like the equals operator. An underscore (_) in pattern stands for (matches) any single character; a percent sign (%) matches any sequence of zero or more characters.