pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedproblems with array
5+ messages / 4 participants
[nested] [flat]
* problems with array
@ 2005-10-15 08:49 paperinik 100 <paperinik_100@hotmail.com>
0 siblings, 2 replies; 5+ messages in thread
From: paperinik 100 @ 2005-10-15 08:49 UTC (permalink / raw)
To: pgsql-sql
PostgreSQL is 7.4.7.
My first table
CREATE TABLE tb_cat (
id INTEGER,
desc text
);
INSERT INTO tb_cat VALUES (10, 'cat10');
INSERT INTO tb_cat VALUES (20, 'cat20');
INSERT INTO tb_cat VALUES (30, 'cat30');
My second table
CREATE TABLE tb_array(
id INTEGER,
cat INTEGER[]
);
INSERT INTO tb_array VALUES(1, ARRAY [10, 20]);
When I write my select
SELECT * from tb_cat WHERE id IN (SELECT cat FROM tb_array WHERE id=1);
the output is:
ERROR: operator does not exist: integer = integer[]
HINT: No operator matches the given name and argument type(s). You may need
to add explicit type casts.
Can anyone help me?
thanks.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: problems with array
@ 2005-10-19 03:09 Matthew Peter <survivedsushi@yahoo.com>
parent: paperinik 100 <paperinik_100@hotmail.com>
1 sibling, 1 reply; 5+ messages in thread
From: Matthew Peter @ 2005-10-19 03:09 UTC (permalink / raw)
To: paperinik 100 <paperinik_100@hotmail.com>; pgsql-sql
Not sure if you got this figured out but I think
SELECT * from tb_cat WHERE id IN (SELECT
array_to_string(cat,',') as cat FROM tb_array WHERE
id=1);
is what your looking for?
--- paperinik 100 <paperinik_100@hotmail.com> wrote:
> PostgreSQL is 7.4.7.
>
> My first table
> CREATE TABLE tb_cat (
> id INTEGER,
> desc text
> );
> INSERT INTO tb_cat VALUES (10, 'cat10');
> INSERT INTO tb_cat VALUES (20, 'cat20');
> INSERT INTO tb_cat VALUES (30, 'cat30');
>
> My second table
> CREATE TABLE tb_array(
> id INTEGER,
> cat INTEGER[]
> );
> INSERT INTO tb_array VALUES(1, ARRAY [10, 20]);
>
> When I write my select
> SELECT * from tb_cat WHERE id IN (SELECT cat FROM
> tb_array WHERE id=1);
> the output is:
> ERROR: operator does not exist: integer = integer[]
> HINT: No operator matches the given name and
> argument type(s). You may need
> to add explicit type casts.
>
> Can anyone help me?
> thanks.
>
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
> http://archives.postgresql.org
>
__________________________________
Yahoo! Music Unlimited
Access over 1 million songs. Try it free.
http://music.yahoo.com/unlimited/
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: problems with array
@ 2005-10-19 03:35 Michael Fuhr <mike@fuhr.org>
parent: Matthew Peter <survivedsushi@yahoo.com>
0 siblings, 1 reply; 5+ messages in thread
From: Michael Fuhr @ 2005-10-19 03:35 UTC (permalink / raw)
To: Matthew Peter <survivedsushi@yahoo.com>; +Cc: paperinik 100 <paperinik_100@hotmail.com>; pgsql-sql
On Tue, Oct 18, 2005 at 08:09:48PM -0700, Matthew Peter wrote:
> Not sure if you got this figured out but I think
>
> SELECT * from tb_cat WHERE id IN (SELECT
> array_to_string(cat,',') as cat FROM tb_array WHERE
> id=1);
>
> is what your looking for?
I doubt it, considering that it doesn't work :-(
SELECT * from tb_cat WHERE id IN (SELECT
array_to_string(cat,',') as cat FROM tb_array WHERE
id=1);
id | desc
----+------
(0 rows)
This might do the trick:
SELECT c.*
FROM tb_cat AS c, tb_array AS a
WHERE c.id = ANY (a.cat) AND a.id = 1;
id | desc
----+-------
10 | cat10
20 | cat20
(2 rows)
Or if you prefer the explicit join syntax:
SELECT c.*
FROM tb_cat AS c JOIN tb_array AS a ON c.id = ANY (a.cat)
WHERE a.id = 1;
id | desc
----+-------
10 | cat10
20 | cat20
(2 rows)
--
Michael Fuhr
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: problems with array
@ 2005-10-19 05:54 Matthew Peter <survivedsushi@yahoo.com>
parent: Michael Fuhr <mike@fuhr.org>
0 siblings, 0 replies; 5+ messages in thread
From: Matthew Peter @ 2005-10-19 05:54 UTC (permalink / raw)
To: Michael Fuhr <mike@fuhr.org>; +Cc: pgsql-sql
Ya, I didn't test it.
The error message was expecting an integer not an
array, so coverting it to a list crossed my mind
'assuming' the subselect 'could' return a string of
integers for the IN clause.
Oh well. I'm glad there's people like you test it.
--- Michael Fuhr <mike@fuhr.org> wrote:
> I doubt it, considering that it doesn't work :-(
__________________________________
Yahoo! Music Unlimited
Access over 1 million songs. Try it free.
http://music.yahoo.com/unlimited/
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: problems with array
@ 2005-11-30 17:58 george young <gry@ll.mit.edu>
parent: paperinik 100 <paperinik_100@hotmail.com>
1 sibling, 0 replies; 5+ messages in thread
From: george young @ 2005-11-30 17:58 UTC (permalink / raw)
To: pgsql-sql
On Sat, 15 Oct 2005 08:49:15 +0000
"paperinik 100" <paperinik_100@hotmail.com> threw this fish to the penguins:
> PostgreSQL is 7.4.7.
>
> My first table
> CREATE TABLE tb_cat (
> id INTEGER,
> desc text
> );
> INSERT INTO tb_cat VALUES (10, 'cat10');
> INSERT INTO tb_cat VALUES (20, 'cat20');
> INSERT INTO tb_cat VALUES (30, 'cat30');
>
> My second table
> CREATE TABLE tb_array(
> id INTEGER,
> cat INTEGER[]
> );
> INSERT INTO tb_array VALUES(1, ARRAY [10, 20]);
>
> When I write my select
> SELECT * from tb_cat WHERE id IN (SELECT cat FROM tb_array WHERE id=1);
> the output is:
> ERROR: operator does not exist: integer = integer[]
> HINT: No operator matches the given name and argument type(s). You may need
> to add explicit type casts.
Use the "any" function (pseudo function? builtin? whatever); no subquery is needed:
select c.* from tb_cat c,tb_array a where a.id=1 and c.id=any(a.cat);
Look at section 8.10.5 "Searching in Arrays" in
http://www.postgresql.org/docs/7.4/interactive/arrays.html
and section 9.17.3 in:
http://www.postgresql.org/docs/7.4/interactive/functions-comparisons.html
-- George Young
--
"Are the gods not just?" "Oh no, child.
What would become of us if they were?" (CSL)
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2005-11-30 17:58 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-10-15 08:49 problems with array paperinik 100 <paperinik_100@hotmail.com>
2005-10-19 03:09 ` Matthew Peter <survivedsushi@yahoo.com>
2005-10-19 03:35 ` Michael Fuhr <mike@fuhr.org>
2005-10-19 05:54 ` Matthew Peter <survivedsushi@yahoo.com>
2005-11-30 17:58 ` george young <gry@ll.mit.edu>
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