Postgresql Text Array get rows NOT LIKE certain value?

Question

Let's say I have a simple table such as this one:

id_ | tags
---------------
0   | foo1, baz
1   | bar1, qux

The id_ column is of type SERIAL and the tags column is of type TEXT[] (text array).

In order to search the tags column with the LIKE operator I use a combination of unnest and DISTINCT ON like this:

SELECT DISTINCT ON (id_) *
FROM (
  SELECT unnest(tags) tag, *
  FROM Records
) x
WHERE
  (tag LIKE '%o%');

This works fine. The query returns row 0 like it should.

Now I'm trying to figure out a way to invert the query, so that it only returns rows that don't match the LIKE expression. I tried with this:

WHERE
  (tag NOT LIKE '%o%');

but it doesn't seem to work... My idea was that this query should return row 1 only, but it returns both rows.

I also tried with sub queries, for example like this one:

    WHERE
      (x.id_ NOT IN (SELECT id_ FROM Records WHERE tag like '%o%'));

But it still returns both rows.

Does anyone know how this can be fixed?

Answer 1

You could use ALL to check, that all unnested tags are NOT LIKE '%o%' by using Postgres' ability to return Booleans for comparison expressions.

SELECT DISTINCT ON (id_) *
       FROM records
       WHERE true = ALL (SELECT tag NOT LIKE '%o%'
                                FROM unnest(tags) tag);

If one tag is LIKE '%o', NOT LIKE '%o%' will return false for that tag and no longer are all selected vales true what would be necessary for true = ALL (...) to be true.

BTW, you could also easily negate this using ANY and LIKE instead:

SELECT DISTINCT ON (id_) *
       FROM records
       WHERE true = ANY (SELECT tag LIKE '%o%'
                                FROM unnest(tags) tag);

(or possibly other combinations of true or false, = ALL or = ANY, (SELECT tag LIKE ...) or (SELECT tag NOT LIKE ...))

SQL Fiddle

Answer 2

like only has to match one tag. not like has to match all of them. Hence, aggregation:

SELECT id_
FROM (SELECT unnest(tags) tag, *
      FROM Records
     ) x
GROUP BY id_
HAVING SUM( (tag LIKE '%o%')::int) = 0;

Or use bool_and():

HAVING bool_and( (tag LIKE '%o%')::int);