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?

Answer 1

Try this instead:

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

Answer 2

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.