Postgresql Not Ilike Clause Does Not Include Null String Values

postgresql NOT ILIKE clause does not include null string values

In SQL, NULL is not equal to anything. Nor is it unequal to anything.

In other words, if I don't tell you my middle name, and you don't tell me your middle name, how can we know if our two middle names are the same name or different names? We can't know.

This often trips people up in SQL, because it's "tri-value logic." An expression can be TRUE, FALSE, or UNKNOWN. Those of us familiar with boolean algebra know that NOT TRUE is FALSE, and NOT FALSE is TRUE.

But the tricky part is that NOT UNKNOWN is still UNKNOWN.

So the solution for you is either always store a non-null string in your column, or else use an expression to account for tri-value logic:

SELECT * FROM table WHERE some_text NOT ILIKE "%anything%' OR some_text IS NULL;

Or:

SELECT * FROM table WHERE COALESCE(some_text, '') NOT ILIKE '%anything%';

PostgreSQL also supports a null-safe equality operator:

SELECT * FROM table WHERE some_text IS DISTINCT FROM 'anything';

But unfortunately, this works only for equality, not for LIKE/ILIKE with patterns and wildcards.

PostgreSQL NULL value cannot be found

As others have said, the problem here is, that you're comparing against a null value, so it returns nothing, because it considers it as false, and I'll go even further that even if you say where name <> 'admf' it wont work, and even if you add more rows it will ignore the null row, and it's not just in PostgreSQL, it doesn't work in SQL-Server or MySQL either.

As you can see in these db<>fiddles SQL-Server, MySQL, and PostgreSQL.

And the reason why it doesn't work is, because you're saying name should not equal a specific value. First name needs to be a value it should not be equal to a value, but when name is null it doesn't have a value, and even more for a side note null itself is not equal null.

The way to solve it is to convert it to a empty string by using COALESCE(name,'') or in SQL-Server you can also use isnull(name,''), and then compare it, or you can add or name is null which will return you all rows, including null, where name <> 'some value'.

Behaviour of NOT LIKE with NULL values

About `NULL`

'anything' NOT LIKE NULL yields NULL, not TRUE.

And only TRUE qualifies for filter expressions in a WHERE clause.

Most functions return NULL on NULL input (there are exceptions). That's the nature of NULL in any proper RDBMS.

If you desire a single expression, you could use:

AND   (column_default LIKE 'nextval%')  IS NOT TRUE;

That's hardly shorter or faster, though. Details in the manual.

Proper query

Your query is still unreliable. A table name alone is not unique in a Postgres database, you need to specify the schema name in addition or rely on the current search_path to find the first match in it:

How does the search_path influence identifier resolution and the "current schema"

SELECT column_name
FROM   information_schema.columns
WHERE  table_name = 'hstore1'
AND    table_schema = 'public'   -- your schema!
AND   (column_default IS NULL OR
       column_default NOT LIKE 'nextval%');

Better, but still not bullet-proof. A column default starting with 'nextval' does not make a serial, yet. See:

Auto increment table column

To be sure, check whether the sequence in use is "owned" by the column with pg_get_serial_sequence(table_name, column_name).

I rarely use the information schema myself. Those slow, bloated views guarantee portability across major versions - and aim at portability to other standard-compliant RDBMS. But too much is incompatible anyway. Oracle does not even implement the information schema (as of 2015).

Also, useful Postgres-specific columns are missing in the information schema. For this case I might query the the system catalogs like this:

SELECT *
FROM   pg_catalog.pg_attribute a
WHERE  attrelid = 'table1'::regclass
AND    NOT attisdropped   -- no dropped (dead) columns
AND    attnum > 0         -- no system columns
AND    NOT EXISTS (
   SELECT FROM pg_catalog.pg_attrdef d
   WHERE  (d.adrelid, d.adnum) = (a.attrelid, a.attnum)
   AND    d.adsrc LIKE 'nextval%'
   AND    pg_get_serial_sequence(a.attrelid::regclass::text, a.attname) <> ''
   );

Faster and more reliable, but less portable.

The manual:

The catalog pg_attrdef stores column default values. The main
information about columns is stored in pg_attribute (see below). Only
columns that explicitly specify a default value (when the table is
created or the column is added) will have an entry here.

'table1'::regclass uses the search_path to resolve the name, which avoids ambiguity. You can schema-qualify the name to overrule: 'myschema.table1'::regclass.

Find the referenced table name using table, field and schema name
Get the default values of table columns in Postgres?

Postgresql: How to get null values when using WHERE col NOT LIKE foo?

The SQL standard says this about NULL in Framework, 4.4.2:

[...] the null value is neither equal to any other value nor not equal to any other value – it is unknown whether or not it is equal to any given value [...]

In 4.4.3.3 it says:

A value of the Boolean data type is either true or false. The truth value of unknown is sometimes represented by the null value.

In its quirky way, it says that comparing something to the NULL values will result in NULL. Think of it this way: it is unknown if an unknown string is like foo.

There are several ways to get what you want:

Use coalesce:

WHERE coalesce(my_text_column, 'foo') LIKE 'foo%'

Use an OR:

WHERE my_text_column LIKE 'foo%' OR my_text_column IS NULL

use UNION ALL:

SELECT count(id)
FROM (SELECT id FROM public.mytable
      WHERE my_text_column LIKE 'foo%'
      UNION ALL
      SELECT id FROM public.mytable
      WHERE my_text_column IS NULL) AS subq;

Queries like that make indexing complicated.

PostgreSQL: select where query filtering out records with [null] values

I've found the answer here. Here's a direct quote from that answer:

In SQL, NULL is not equal to anything. Nor is it unequal to anything.
In other words, if I don't tell you my middle name, and you don't tell
me your middle name, how can we know if our two middle names are the
same name or different names? We can't know.
This often trips people up in SQL, because it's "tri-value logic." An
expression can be TRUE, FALSE, or UNKNOWN. Those of us familiar with
boolean algebra know that NOT TRUE is FALSE, and NOT FALSE is TRUE.
But the tricky part is that NOT UNKNOWN is still UNKNOWN.
So the solution for you is either always store a non-null string in
your column, or else use an expression to account for tri-value logic

This works:

SELECT gateway, customer_id
FROM gateways
where gateway = '1000056'
    and customer_id is distinct from 21

Returning the record I want:
the record I want to be returned

ILIKE and NOT ILIKE in aws redshift different from total

Double-quotes are for identifiers: "myColumn"

Single quotes are for values: 'value'.

Your examples contradict those basic syntax rules.

Also, you did not consider NULL values, which neither qualify with:

item_name ilike 'blue'

nor with:

item_name not ilike 'blue'

What do you get for:

SELECT count(*)                             AS all_rows
     , count(item_name  ~~* 'blue' OR NULL) AS item_name_blue
     , count(item_name !~~* 'blue' OR NULL) AS item_name_not_blue
     , count(item_name)                     AS item_name_not_null
     , count(item_name IS NULL OR NULL)     AS item_name_null
FROM   t1;

~~* .. internal Postgres operator for ILIKE

!~~* .. internal Postgres operator for NOT ILIKE

(Careful: slightly different operator precedence.)

Search for ilike does not work after converting bytea to a text field in PostgreSQL ver.12 (for Cyrillic)

The problem is that the result of convert_from is in the C collation:

On my system, I have the following:

SHOW lc_collate;

 lc_collate 
════════════
 de_AT.utf8
(1 row)

SELECT pg_collation_for('Берёза');

 pg_collation_for 
══════════════════
 (null)
(1 row)

The NULL value means that the default collation (lc_collate) will be used for comparisons:

SELECT 'Берёза' ilike 'берёз%';

 ?column? 
══════════
 t
(1 row)

Now if we use convert_from, the collation is different, so the comparison is different:

SELECT pg_collation_for(convert_from(convert_to('Берёза', 'UTF8'), 'UTF8'));

 pg_collation_for 
══════════════════
 "C"
(1 row)

select convert_from(convert_to('Берёза', 'UTF8'), 'UTF8') ilike 'берёз%';

 ?column? 
══════════
 f
(1 row)

SELECT 'Берёза' COLLATE "C" ilike 'берёз%';
 ?column? 
══════════
 f
(1 row)

So you can solve the problem by explicitly specifying the default collation:

select convert_from(convert_to('Берёза', 'UTF8'), 'UTF8') COLLATE "default" ilike 'берёз%';

 ?column? 
══════════
 t
(1 row)

Best way to check for empty or null value

The expression stringexpression = '' yields:

TRUE .. for '' (or for any string consisting of only spaces with the data type char(n))

NULL .. for NULL

FALSE .. for anything else

So to check for: "stringexpression is either NULL or empty":

(stringexpression = '') IS NOT FALSE

Or the reverse approach (may be easier to read):

(stringexpression <> '') IS NOT TRUE

Works for any character type including char(n). The manual about comparison operators.

Or use your original expression without trim(), which is costly noise for char(n) (see below), or incorrect for other character types: strings consisting of only spaces would pass as empty string.

coalesce(stringexpression, '') = ''

But the expressions at the top are faster.

Asserting the opposite is even simpler: "stringexpression is neither NULL nor empty":

stringexpression <> ''

About `char(n)`

This is about the data type char(n), short for: character(n). (char / character are short for char(1) / character(1).) Its use is discouraged in Postgres:

In most situations text or character varying should be used instead.

Do not confuse char(n) with other, useful, character types varchar(n), varchar, text or "char" (with double-quotes).

In char(n) an empty string is not different from any other string consisting of only spaces. All of these are folded to n spaces in char(n) per definition of the type. It follows logically that the above expressions work for char(n) as well - just as much as these (which wouldn't work for other character types):

coalesce(stringexpression, '  ') = '  '
coalesce(stringexpression, '') = '       '

Demo

Empty string equals any string of spaces when cast to char(n):

SELECT ''::char(5) = ''::char(5)     AS eq1
     , ''::char(5) = '  '::char(5)   AS eq2
     , ''::char(5) = '    '::char(5) AS eq3;

Result:

 eq1 | eq2 | eq3
 ----+-----+----
 t   | t   | t

Test for "null or empty string" with char(n):

SELECT stringexpression 
     , stringexpression = ''                   AS base_test
     , (stringexpression = '')  IS NOT FALSE   AS test1
     , (stringexpression <> '') IS NOT TRUE    AS test2
     , coalesce(stringexpression, '') = ''     AS coalesce1
     , coalesce(stringexpression, '  ') = '  ' AS coalesce2
     , coalesce(stringexpression, '') = '  '   AS coalesce3
FROM  (
   VALUES
     ('foo'::char(5))
   , ('')
   , ('   ')                -- not different from '' in char(n)
   , (NULL)
   ) sub(stringexpression);

Result:


 stringexpression | base_test | test1 | test2 | coalesce1 | coalesce2 | coalesce3 
------------------+-----------+-------+-------+-----------+-----------+-----------
 foo              | f         | f     | f     | f         | f         | f
                  | t         | t     | t     | t         | t         | t
                  | t         | t     | t     | t         | t         | t
 null             | null      | t     | t     | t         | t         | t

Test for "null or empty string" with text:

SELECT stringexpression 
     , stringexpression = ''                   AS base_test
     , (stringexpression = '')  IS NOT FALSE   AS test1
     , (stringexpression <> '') IS NOT TRUE    AS test2
     , coalesce(stringexpression, '') = ''     AS coalesce1
     , coalesce(stringexpression, '  ') = '  ' AS coalesce2
     , coalesce(stringexpression, '') = '  '   AS coalesce3
FROM  (
   VALUES
     ('foo'::text)
   , ('')
   , ('   ')                -- different from '' in a sane character types
   , (NULL)
   ) sub(stringexpression);

Result:


 stringexpression | base_test | test1 | test2 | coalesce1 | coalesce2 | coalesce3 
------------------+-----------+-------+-------+-----------+-----------+-----------
 foo              | f         | f     | f     | f         | f         | f
                  | t         | t     | t     | t         | f         | f
                  | f         | f     | f     | f         | f         | f
 null             | null      | t     | t     | t         | t         | f

db<>fiddle here

_{Old sqlfiddle}

Any downsides of using data type "text" for storing strings?

Postgres query for IN(NULL, 'test') does not work

You can't compare NULL values using = (which is what IN is doing).

Quote from the manual

Ordinary comparison operators yield null (signifying “unknown”), not true or false, when either input is null. For example, 7 = NULL yields null, as does 7 <> NULL

You need to add a check for NULL explicitly:

SELECT * 
FROM table_name 
WHERE (column_name IN ('someTest', 'someOtherTest') OR column_name IS NULL);

NOT LIKE and LIKE not returning opposite result

Does this return the correct result ?

Select * from tbl1 WHERE COALESCE([TextCol],'-1') NOT LIKE '%TAX%'

I believe NULL values are the issue here, if the column contains them, then NULL NOT LIKE '%TAX%' will return UNKNOWN/NULL and therefore won't be selected.

I advise you to read about handling with NULL values , or here.

As @ughai suggested, if performance is an issue you can also use:

  Select * from tbl1 
  WHERE [TextCol] NOT LIKE '%TAX%'
     OR [TextCol] IS NULL

Postgresql Not Ilike Clause Does Not Include Null String Values