Query:
WITH first_names AS (SELECT DISTINCT fname FROM voters)
SELECT name
from nicknames
WHERE groupi = (
SELECT nn.groupi
FROM nicknames AS nn
WHERE name = 'john'
) AND name != 'john' AND upper(name) = ANY(first_names);
Error:
column "first_names" does not exist
How do I make an array from a select and use it in a where statement?
WITH first_names AS (SELECT DISTINCT fname FROM voters)
SELECT name
from nicknames
JOIN first_names on fname = upper(name)
WHERE groupi = (SELECT nn.groupi FROM nicknames AS nn WHERE name = 'john') AND name != 'john'
;
You're confusing auxiliary statement names with columns here. first_names is the name for the result provided by your SELECT DISTINCT, and fname is the name of the column contained within.
Not commenting on the correctness of your query overall, the corrected statement would be to use the correct column name.
WITH first_names AS (SELECT DISTINCT fname
FROM voters)
SELECT name
FROM nicknames
WHERE groupi = (
SELECT nn.groupi
FROM nicknames AS nn
WHERE name = 'john'
) AND name != 'john' AND upper(name) = ANY (first_names.fname);