Date: Wed, 30 Nov 2005 12:58:22 -0500
From: george young <gry@ll.mit.edu>
To: pgsql-sql@postgresql.org
Subject: Re: problems with array
Message-Id: <20051130125822.7af50889.gry@ll.mit.edu>
In-Reply-To: <BAY108-F2573522163DF0C1FC43D0DD27C0@phx.gbl>
References: <BAY108-F2573522163DF0C1FC43D0DD27C0@phx.gbl>
Reply-To: gry@ll.mit.edu
Organization: MIT Lincoln Laboratory
Mime-Version: 1.0
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: 7bit

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)