pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedSubselects returning array and ANY...
4+ messages / 2 participants
[nested] [flat]
* Subselects returning array and ANY...
@ 2008-02-28 00:01 Dawid Kuroczko <qnex42@gmail.com>
2008-02-28 00:11 ` Re: Subselects returning array and ANY... Josh Berkus <josh@agliodbs.com>
0 siblings, 1 reply; 4+ messages in thread
From: Dawid Kuroczko @ 2008-02-28 00:01 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Subselects returning array and ANY...
2008-02-28 00:01 Subselects returning array and ANY... Dawid Kuroczko <qnex42@gmail.com>
@ 2008-02-28 00:11 ` Josh Berkus <josh@agliodbs.com>
2008-02-28 00:34 ` Re: Subselects returning array and ANY... Dawid Kuroczko <qnex42@gmail.com>
0 siblings, 1 reply; 4+ messages in thread
From: Josh Berkus @ 2008-02-28 00:11 UTC (permalink / raw)
To: pgsql-sql; +Cc: Dawid Kuroczko <qnex42@gmail.com>
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Subselects returning array and ANY...
2008-02-28 00:01 Subselects returning array and ANY... Dawid Kuroczko <qnex42@gmail.com>
2008-02-28 00:11 ` Re: Subselects returning array and ANY... Josh Berkus <josh@agliodbs.com>
@ 2008-02-28 00:34 ` Dawid Kuroczko <qnex42@gmail.com>
2008-02-28 00:45 ` Re: Subselects returning array and ANY... Josh Berkus <josh@agliodbs.com>
0 siblings, 1 reply; 4+ messages in thread
From: Dawid Kuroczko @ 2008-02-28 00:34 UTC (permalink / raw)
To: josh@agliodbs.com; +Cc: pgsql-sql
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 ;))
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Subselects returning array and ANY...
2008-02-28 00:01 Subselects returning array and ANY... Dawid Kuroczko <qnex42@gmail.com>
2008-02-28 00:11 ` Re: Subselects returning array and ANY... Josh Berkus <josh@agliodbs.com>
2008-02-28 00:34 ` Re: Subselects returning array and ANY... Dawid Kuroczko <qnex42@gmail.com>
@ 2008-02-28 00:45 ` Josh Berkus <josh@agliodbs.com>
0 siblings, 0 replies; 4+ messages in thread
From: Josh Berkus @ 2008-02-28 00:45 UTC (permalink / raw)
To: Dawid Kuroczko <qnex42@gmail.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2008-02-28 00:45 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2008-02-28 00:01 Subselects returning array and ANY... Dawid Kuroczko <qnex42@gmail.com>
2008-02-28 00:11 ` Josh Berkus <josh@agliodbs.com>
2008-02-28 00:34 ` Dawid Kuroczko <qnex42@gmail.com>
2008-02-28 00:45 ` Josh Berkus <josh@agliodbs.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox