Find value in array (Postgresql)

Question

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

klin · Accepted Answer · 2018-09-06 16:05:55Z

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
)

Lukasz Szozda · Accepted Answer · 2018-09-06 14:35:34Z

1

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

answered Sep 6, 2018 at 14:35

Lukasz Szozda

181k26 gold badges278 silver badges326 bronze badges

Collectives™ on Stack Overflow

Find value in array (Postgresql)

2 Answers 2

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Related