Thread: problems with array

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.

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/

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 | cat1020 | 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 | cat1020 | cat20
(2 rows)

-- 
Michael Fuhr

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/

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)