Using an array from a CTE in SELECT ... WHERE ... ANY

user_3733070 0
Viewed 2863

I want to create an array of IDs once in a CTE and use it in multiple ANYs. But I'm getting stuck at the first one. Using PG 9.3

WITH x AS (
    SELECT ARRAY(SELECT * FROM generate_series(1, 10)) AS a
)
SELECT 1
WHERE 2 = ANY(SELECT a FROM x)

What I expect is that the SELECT statement in ANY will return the previously created array. Instead I get this error:

ERROR: operator does not exist: integer = integer[]

LINE 6: WHERE 2 = ANY(SELECT a FROM x)

I don't understand what the issue is because the select statement should return an array, and ANY takes an array.

I could of course not create the array in the CTE and create it on the fly in ANY every time, but I assume that would be less performant than creating it once. And I must have an array, because ANY without the array changes the query plan (when doing this on my actual table, not this small example) to something less performant.

postgresql sql
2 Answers

There are two forms of ANY. If you want to use ANY(array expression):

WITH x AS (
    SELECT ARRAY(SELECT * FROM generate_series(1, 10)) AS a
)

SELECT 1
FROM x
WHERE 2 = ANY(a)

or if you want to use ANY(subquery):

WITH x AS (
    SELECT * FROM generate_series(1, 10) AS a
)

SELECT 1
WHERE 2 = ANY(SELECT a FROM x)
user_19957380

The normal SQL method for doing this does not use arrays:

WITH x AS (
      SELECT gs.a
      FROM generate_series(1, 10)) AS gs(a)
     )
SELECT 1
WHERE 2 IN (SELECT a FROM x);

Of course, you might want to be using arrays for a different reason (such as learning about arrays). But you should at a minimum understand the SQL way to solve this problem.

user_11440350
Related