PostgreSQL - Comparison operator with character varying - Exclude values

Question

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.

LIKE without a wildcard makes no sense LIKE 'A' is exactly the same as = 'A' and most probably faster as well. — user330315
– user330315, Commented Mar 15, 2022 at 16:03
The NULL is causing the problem. Try scope IS DISTINCT FROM 'A'. For explanation see Comparison. — user7070613
– user7070613, Commented Mar 15, 2022 at 16:04

user330315 · Accepted Answer · 2022-03-15 16:05:06Z

3

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';

answered Mar 15, 2022 at 16:05

user330315

Sign up to request clarification or add additional context in comments.

3 Comments

GeoGyro Over a year ago

Ok Why do you say 'safe comparison' ? Official documentation says: <> is the standard SQL notation for “not equal”. != is an alias, so I don't understand the difference ?

user330315 Over a year ago

@GeoGyro: where do I use <> in my query? is distinct from is the null safe variant of <>. Quote from the page you linked: "Not equal, treating null as a comparable value."

GeoGyro Over a year ago

I thought <> handle NULL values. I was wrong.

user51187286016 · Accepted Answer · 2022-03-15 16:12:44Z

1

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.

answered Mar 15, 2022 at 16:12

user51187286016

2861 silver badge5 bronze badges

Comments

jian · Accepted Answer · 2022-03-15 16:14:01Z

1

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.

answered Mar 15, 2022 at 16:14

jian

5,0231 gold badge29 silver badges47 bronze badges

Collectives™ on Stack Overflow

PostgreSQL - Comparison operator with character varying - Exclude values

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related