CODEWITHSUNDEEP
sqldatabasepostgresql
rococo
rococo

Reputation: 2657

Postgres - Check if array contains values other than expected ones

Say one entry has the array value {1, 2, 3}.

I want to be able to query this question:

"Does the array contain numbers other than 1 and 2?" (yes)

This would also work for my purposes:

"Does this array contain no values other than 1, 2, 3, and 4?" (yes - ok even though 4 isn't used)

I've looked around and haven't been able to find this particular variant of searching in an array. Is there an efficient way to do this?

Edit: I've hacked together a solution where I unnest the array, and select from the unnested rows to match my condition with an != ANY, but it doesn't seem to be very elegant and I'm hoping there's an alternative!

Upvotes: 2

Views: 471

Answers (1)

Kaushik Nayak
Kaushik Nayak

Reputation: 31648

Just an option you could try using EXCEPT and UNNEST

SELECT CASE 
        WHEN count(*) > 0
            THEN TRUE
        ELSE FALSE
        END IN_A_NOT_IN_B
FROM (
    SELECT unnest(array [1,2,3])
        EXCEPT
    SELECT unnest(array [1,2])
    ) t;

If you reverse it, your second condition would be True.

Upvotes: 3

Related Questions