CODEWITHSUNDEEP
sqlarrayspostgresqlselect
douglas_forsell
douglas_forsell

Reputation: 131

Find value in array (Postgresql)

The following returns "{3130,3135,2179,3186}", type "INTEGER[]":

SELECT
array_agg(c) AS c
FROM
(
SELECT 3130::INTEGER AS c
UNION
SELECT 3135::INTEGER AS c
UNION
SELECT 2179::INTEGER AS c
UNION
SELECT 3186::INTEGER AS c
) AS v

If I try to find the number "3186" by directly converting the text to an integer array, it works just fine:

SELECT 3186::INTEGER = ANY
(
'{3130,3135,2179,3186}'::INTEGER[]
)

But if I try doing the same with the result from the first query, I get an "operator does not exist: integer = integer[]" error message:

SELECT 3186::INTEGER = ANY
(
    SELECT
    array_agg(c) AS c
    FROM
    (
    SELECT 3130::INTEGER AS c
    UNION
    SELECT 3135::INTEGER AS c
    UNION
    SELECT 2179::INTEGER AS c
    UNION
    SELECT 3186::INTEGER AS c
    ) AS v
)

What am I doing wrong?

I'm Using PostgreSQL 9.5.3, PgAdmin 1.22.1

Upvotes: 1

Views: 777

Answers (2)

klin
klin

Reputation: 121889

There are two forms of ANY(). In your query they are mixed.

If you want to use ANY (array expression):

SELECT 3130 = ANY(
    ARRAY(
        SELECT 3130::INTEGER AS c
        UNION
        SELECT 3135::INTEGER AS c
        UNION
        SELECT 2179::INTEGER AS c
        UNION
        SELECT 3186::INTEGER AS c
    ) 
)

or 

SELECT 3130 = ANY(array_agg(c))
FROM (
    SELECT 3130::INTEGER AS c
    UNION
    SELECT 3135::INTEGER AS c
    UNION
    SELECT 2179::INTEGER AS c
    UNION
    SELECT 3186::INTEGER AS c
    ) alias

If you want to use ANY (subquery):

SELECT 3130 = ANY(
    SELECT 3130::INTEGER AS c
    UNION
    SELECT 3135::INTEGER AS c
    UNION
    SELECT 2179::INTEGER AS c
    UNION
    SELECT 3186::INTEGER AS c
)

Upvotes: 2

Lukasz Szozda
Lukasz Szozda

Reputation: 176254

You could explicitly cast to INTEGER[]:

SELECT 3186::INTEGER = ANY(
(
    SELECT array_agg(c) AS c
    FROM (
       SELECT 3130::INTEGER AS c
       UNION
       SELECT 3135::INTEGER AS c
       UNION
       SELECT 2179::INTEGER AS c
       UNION
       SELECT 3186::INTEGER AS c
    ) AS v
)::INTEGER[]);

DBFiddle Demo

Upvotes: 1

Related Questions