postgresql: any on subquery returning array

Question

I have a user_lists table that contains a user_list column of type integer[].

I'm trying to do this query, which seems basic enough:

select id, alias from users where id = ANY(select user_list from user_lists where id = 2 limit 1);

It gives this error:

ERROR: operator does not exist: integer = integer[]
HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts.

I'm using postgres 8.3.11. Upgrading is not an option.

What am I missing?

score 6 · Accepted Answer · edited Jun 16 '17 at 13:58

6

Try this instead:

select id, alias from users 
where (select user_list from user_lists where id = 2 limit 1) 
@> ARRAY[id];

edited Jun 16 '17 at 13:58

flashton

208
1
2
8

answered May 18 '12 at 09:57

Adrian Serafin

7,665
5
46
67

Whoah. Magic. Care to explain this? – Ty Kroll May 18 '12 at 09:59
2

ARRAY_1 @> ARRAY_2 means "ARRAY_1 contains ARRAY_2" – Adrian Serafin May 18 '12 at 10:01

score 1 · Answer 2 · answered May 18 '12 at 11:03

You can also try something like this (ought to work on 8.3, don't have one to hand):

SELECT u.id, u.alias 
FROM users u JOIN user_lists ul ON u.id = ANY(ul.user_list) 
WHERE ul.id = 2;

Oh, you're missing some bugfixes (8.3.18 is current) and I'd expect 8.3 to be end-of-life soon, so upgrading really needs to be an option in the next year or so.

postgresql: any on subquery returning array

2 Answers2