Postgres array Case issue [duplicate]

Question

I'm trying to write a simple query with the array function in PostgreSQL, but it doesn't seem to be working properly.

WITH vars AS (
  SELECT array['1114156957', '1234'] as npi
)
SELECT CASE 
         when '1114156957' <> ANY(npi) then 'Not Found'
         ELSE 'found'
       End as test
FROM vars;

I'm new to Postgres and have never used the array function before. But in the above query shouldn't the result be "Found" since one of values exists in the array?

user330315 · Accepted Answer · 2018-09-18 14:09:04Z

3

'1114156957' <> ANY(..) means: "'1114156957' is not equal to any of the values in the array. Or to put it the other way round: "is not equal to at least one element in the array".

As there is one value that indeed is not equal, the condition '1114156957' <> ANY(npi) is true.

If you want to check if an element is not contained at all in the array, you need to use <> ALL()

WITH vars(npi) AS (
   values (array['1114156957', '1234'])
)
SELECT CASE 
         when '1114156957' = ALL(npi) then 'Not Found'
         ELSE 'found'
       End as test
FROM vars;

returns 'Found'

Online example: http://rextester.com/UDBNH6876

answered Sep 18, 2018 at 14:09

user330315

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

Collectives™ on Stack Overflow

Postgres array Case issue [duplicate]

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related