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?

flashton · Accepted Answer · 2017-06-16 13:58:14Z

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, 2017 at 13:58

flashton

2281 gold badge2 silver badges9 bronze badges

answered May 18, 2012 at 9:57

Adrian Serafin

7,7315 gold badges49 silver badges70 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Ty Kroll Over a year ago

Whoah. Magic. Care to explain this?

Adrian Serafin Over a year ago

ARRAY_1 @> ARRAY_2 means "ARRAY_1 contains ARRAY_2"

Richard Huxton · Accepted Answer · 2012-05-18 11:03:36Z

1

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.

answered May 18, 2012 at 11:03

Richard Huxton

23.6k5 gold badges43 silver badges53 bronze badges

Collectives™ on Stack Overflow

postgresql: any on subquery returning array

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related