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?

sticky bit · Accepted Answer · 2018-05-29 00:09:19Z

2

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

edited May 29, 2018 at 0:09

answered May 28, 2018 at 23:59

sticky bit

37.7k12 gold badges34 silver badges46 bronze badges

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

2 Comments

jpmc26 Over a year ago

You don't need the true = part. PG is good about Booleans, unlike SQL Server and Oracle.

sticky bit Over a year ago

@jpmc26: I do need it, as the ALL or ANY operator needs something to compare to. Just WHERE ALL (...) throws a syntax error.

Rotareti · Accepted Answer · 2018-05-30 17:30:20Z

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

edited May 30, 2018 at 17:30

Rotareti

54.7k24 gold badges122 silver badges115 bronze badges

answered May 28, 2018 at 21:53

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

2 Comments

jpmc26 Over a year ago

Why convert to int and use SUM instead of the dedicated bool_and and bool_or aggregate functions?

Gordon Linoff Over a year ago

@jpmc26 . . . Good point. It is a habit because I use a lot of different databases.

Collectives™ on Stack Overflow

Postgresql Text Array get rows NOT LIKE certain value?

2 Answers 2

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related