Postgres query regex negative doesnt return expected result

Question

I use the query tool in pgAdmin 4 connected a database. In one of the tables, one of the columns should contain values that are exactly 8 digits (e.g. 12345678).

There are 99 that are exactly 8 digits and 1 that is empty (null).

I would like to create a query that finds the empty one. The following query returns the 99 records that match:

SELECT COUNT (*) FROM my_table WHERE my_column ~ '^[0-9]{8}$';

The problem is that when I add ! (negative, just before the tilde), I get 0 matches rather than 1 as expected:

SELECT COUNT (*) FROM my_table WHERE my_column !~ '^[0-9]{8}$';

Any idea what could go wrong?

Do you mean you also want to find empty strings? WHERE my_column ~ '^([0-9]{8})?$'? — Wiktor Stribiżew
– Wiktor Stribiżew, Commented Mar 31, 2021 at 12:05
Thanks for your reply. No, I want to find those that don't match. See @Gordon Linnof answer below, solved my problem. — user9250533
– user9250533, Commented Mar 31, 2021 at 12:31

Gordon Linoff · Accepted Answer · 2021-03-31 11:59:40Z

3

Almost all comparison operations return NULL when the value is NULL -- and WHERE filters out NULL. So, either add an explicit comparison:

WHERE my_column !~ '^[0-9]{8}$' OR my_column IS NULL

Or use COALESCE():

WHERE COALESCE(my_column, '') !~ '^[0-9]{8}$'

answered Mar 31, 2021 at 11:59

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

Collectives™ on Stack Overflow

Postgres query regex negative doesnt return expected result

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related