pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  

help / color / mirror / Atom feed

From: george young <gry@ll.mit.edu>
To: pgsql-sql@postgresql.org
Subject: Re: problems with array
Date: Wed, 30 Nov 2005 12:58:22 -0500
Message-ID: <20051130125822.7af50889.gry@ll.mit.edu> (raw)
In-Reply-To: <BAY108-F2573522163DF0C1FC43D0DD27C0@phx.gbl>
References: <BAY108-F2573522163DF0C1FC43D0DD27C0@phx.gbl>

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)

view thread (5+ messages)

Message-ID: <20051130125822.7af50889.gry@ll.mit.edu>
Permalink:  ../20051130125822.7af50889.gry@ll.mit.edu/
Also on:    postgresql.org/message-id/20051130125822.7af50889.gry@ll.mit.edu

copy link · copy postgr.es

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-sql@postgresql.org
  Cc: gry@ll.mit.edu
  Subject: Re: problems with array
  In-Reply-To: <20051130125822.7af50889.gry@ll.mit.edu>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

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