Question 253902 in PostgreSQL Question posted in
The official documentation can be found here.

what is the difference between field_name not like any and not ( field_name like any) in postgresql

These two queries are giving different results in postgresql

  1. not (field_name like any(array['a','b']) is giving values that are not a or b as expected
  2. whereas field_name not like any(array['a','b']) is checking for both a and b values

I basically want to fetch records not having either a or b. I’ve gone through postgres documentation did not find any result

Tags:

2

Answers


  1. 0

    Im not sure what you’re asking for since you already know the difference between the two queries already. The first query is checking for values that are not equal to ‘a’ or ‘b’ which is what you want.

    The second query is checking if ‘field_name’ has any occurrence of the patterns ‘a’ or ‘b’ it will not return the record. In other words, it will return only the records where there are no matching patterns i.e no ‘a’ or ‘b’.

    Login or Signup to reply.
  2. 0

    The 2nd query makes no sense:

    field_name not like any(array['a','b']) is the same as
    field_name not like 'a' OR field_name not like 'b', so except for null, it will always be true (fieldname can’t be a and b at the same time).

    If you want to rewrite the 1st query, you would have to use ALL

    field_name not like ALL(array['a','b']), which is the same as
    field_name not like 'a' AND field_name not like 'b'

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search