Thread: Subselects returning array and ANY...

A simple text case

=# CREATE TEMP TABLE foo (t text);
CREATE TABLE
=# INSERT INTO foo SELECT 'x'||n FROM generate_series(1,100) AS x(n);
INSERT 0 100

This works: SELECT * FROM foo WHERE t = ANY ('{x4,5,zzz}'::text[]);

And this works too: SELECT * FROM foo WHERE t IN (SELECT t FROM foo LIMIT 5);

...now, what am I doing wrong with this query?
  SELECT * FROM foo WHERE t = ANY (SELECT '{x4,5,zzz}'::text[]);
ERROR:  operator does not exist: text = text[]
HINT:  No operator matches the given name and argument type(s). You
might need to add explicit type casts.

I have a table containing text array field, and I want to use this
field in subselect...
I know it must be simple...  (without defining new operators).

Thanks in advance!

Dawid

Dawid,

>    SELECT * FROM foo WHERE t = ANY (SELECT '{x4,5,zzz}'::text[]);
> ERROR:  operator does not exist: text = text[]
> HINT:  No operator matches the given name and argument type(s). You
> might need to add explicit type casts.

Drop the second SELECT, I think.

postgres=# select 'x' = ANY ( '{x,y,z}'::TEXT[] );?column?
----------t


-- 
--Josh

Josh Berkus
PostgreSQL @ Sun
San Francisco

On Thu, Feb 28, 2008 at 1:11 AM, Josh Berkus <josh@agliodbs.com> wrote:
> Dawid,
>  >    SELECT * FROM foo WHERE t = ANY (SELECT '{x4,5,zzz}'::text[]);
>  > ERROR:  operator does not exist: text = text[]
>  > HINT:  No operator matches the given name and argument type(s). You
>  > might need to add explicit type casts.
>
>  Drop the second SELECT, I think.
>
>  postgres=# select 'x' = ANY ( '{x,y,z}'::TEXT[] );
>   ?column?
>  ----------
>   t

Probably I didn't make myself clear enough. :)  I know this works (see
above two examples).
I need to connect two tables, one is:

CREATE TEMP TABLE haystack (straw text);
INSERT INTO haystack SELECT n FROM generate_series(1,100) AS x(n);

CREATE TEMP TABLE needles (id int, straws text[]);
INSERT INTO needles VALUES (1, '{10,15,aaa}');

SELECT * FROM haystack WHERE straw = ANY (SELECT straws FROM needles
WHERE id=1);
ERROR:  operator does not exist: text = text[]
HINT:  No operator matches the given name and argument type(s). You
might need to add explicit type casts.

But you are right, though!  I just need to leave off the subselect!  Thanks!
SELECT h.* FROM haystack h JOIN needles ON straw = ANY (straws);

(not exactly what I was looking for, but it works ;))

Dawid,

> But you are right, though!  I just need to leave off the subselect! 
> Thanks! SELECT h.* FROM haystack h JOIN needles ON straw = ANY (straws);
>
> (not exactly what I was looking for, but it works ;))

Yeah, the problem with your first query is that it's returning a rowset of 
arrays, not a single array.  This means that ANY() doesn't know exactly 
what to do with it.

-- 
--Josh

Josh Berkus
PostgreSQL @ Sun
San Francisco