Re: problems with array

On Sat, 15 Oct 2005 08:49:15 +0000
"paperinik 100" <paperinik_100(at)hotmail(dot)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)