Thread: Array with Subselect / ANY - cast?
Hi - I have the following array field:
SELECT pb_ids FROM pb WHERE id = 123:
pb_id
-----------------------
{196,213,215,229,409}
These numbers map to a productid in tblproducts so I figured I could
do this:
SELECT *
FROM tblproducts
WHERE productid = ANY (
SELECT pb_ids FROM pb WHERE id=123
);
This complains: "ERROR: operator does not exist: integer =
integer[]".
This doesn't seem much different than the example in the docs:
SELECT * FROM sal_emp WHERE 10000 = ANY (pay_by_quarter);
If I do this:
SELECT *
FROM tblproducts
WHERE productid = ANY ('{196,213,215,229,409}'});
The query runs fine.
Any ideas on how to make this work?
Thanks!
Josh
P.S. Postgres 8.1.9 on Linux
Hello
2007/8/21, Josh Trutwin <josh@trutwins.homeip.net>:
> Hi - I have the following array field:
>
> SELECT pb_ids FROM pb WHERE id = 123:
>
> pb_id
> -----------------------
> {196,213,215,229,409}
>
> These numbers map to a productid in tblproducts so I figured I could
> do this:
>
> SELECT *
> FROM tblproducts
> WHERE productid = ANY (
> SELECT pb_ids FROM pb WHERE id=123
> );
>
SELECT * FROM ... WHERE 1000 IN (SELECT pb_ids FROM pb WHERE id=123)
or
SELECT * FROM ... WHERE 1000 = ANY(ARRAY(SELECT pb_ids FROM pb WHERE id=123))
Pavel
On Tue, 21 Aug 2007 20:15:59 +0200 "Pavel Stehule" <pavel.stehule@gmail.com> wrote: > SELECT * FROM ... WHERE 1000 IN (SELECT pb_ids FROM pb WHERE id=123) > > or > > SELECT * FROM ... WHERE 1000 = ANY(ARRAY(SELECT pb_ids FROM pb > WHERE id=123)) Thanks - another way: SELECT * FROM ... WHERE 1000 = ANY((SELECT pb_ids FROM pb WHERE id=123)::integer[]) Josh
On Aug 21, 2007, at 12:49 , Josh Trutwin wrote:
> SELECT pb_ids FROM pb WHERE id = 123:
>
> pb_id
> -----------------------
> {196,213,215,229,409}
>
> These numbers map to a productid in tblproducts so I figured I could
> do this:
>
> SELECT *
> FROM tblproducts
> WHERE productid = ANY (
> SELECT pb_ids FROM pb WHERE id=123
> );
Out of curiosity, what led to the schema design of storing these
pb_id values in an array rather than in a many-to-many table? You're
working against the database server here. The usual way to define
this relationship would be
CREATE TABLE pb (id INTEGER PRIMARY KEY);
CREATE TABLE pb_ids
(
id INTEGER NOT NULL REFERENCES pb
, pb_id INTEGER NOT NULL
REFERENCES tblproducts (pb)
, PRIMARY KEY (id, pb)
);
(if I've interpreted the column and table names correctly)
Then your query reduces to a simple
SELECT *
FROM tblproducts
JOIN pb_ids ON (pb_id = pb)
WHERE id = 123;
This reduces the query to straight-forward SQL (which is set based)
rather than wrangling arrays (which are really better considered
opaque from the standpoint of database schema design) and enables
referential integrity using built-in foreign key constraints rather
than requiring custom triggers (to make sure each element of the
pb_id array corresponds to a pb value in tblproducts).
Michael Glaesemann
grzm seespotcode net
On Tue, 21 Aug 2007 14:19:03 -0500 Michael Glaesemann <grzm@seespotcode.net> wrote: > Out of curiosity, what led to the schema design of storing these > pb_id values in an array rather than in a many-to-many table? > You're working against the database server here. The usual way to > define this relationship would be <snip> Yeah - I'm a big proponent of doing things the relational way but for this application it seemed like the right thing (after much debating) to do at the time and it's been working well so far. The app is kind of an object builder that lets any joe schmoe create an object like a student, so you can create a field called fname, lname, etc. and also fields that contain lists of values (phone numbers, majors, etc) - it's a simple example but the number of list-type fields is anticipated to be VERY high so I decided to deal with arrays instead of creating all sorts of normalized tables. Was it the right thing to do? I don't know yet but it's working so far. It gets interesting when you set a field that links to another object - say you have a class object and a student object and you want to say student has many classes - the array in this case store the class id pks but it's not a foreign key as arrays don't do that so I had to put some triggers (as you mentioned) to keep things in line (if a class is deleted for example). These are really poor examples because anyone who does an app for students/classes creates normalized tables (at least for oltp) but the app we're building gives the end user the flexibility to create whatever they can think of. The meta data that this app uses to store information about the objects the user is building is all stored in normalized tables, but the data for the objects themselves I wanted to keep as wide as possible. So yeah, arrays are a PITA but I think for this it'll work, time will tell... Josh
2007/8/21, Josh Trutwin <josh@trutwins.homeip.net>:
> On Tue, 21 Aug 2007 20:15:59 +0200
> "Pavel Stehule" <pavel.stehule@gmail.com> wrote:
>
> > SELECT * FROM ... WHERE 1000 IN (SELECT pb_ids FROM pb WHERE id=123)
> >
> > or
> >
> > SELECT * FROM ... WHERE 1000 = ANY(ARRAY(SELECT pb_ids FROM pb
> > WHERE id=123))
>
> Thanks - another way:
>
> SELECT * FROM ... WHERE 1000 = ANY((SELECT pb_ids FROM pb
> WHERE id=123)::integer[])
>
it works? no.
pavel=# select 1 from (values(10)) a(i) where i = any((select * from
foo)::int[]);
ERROR: cannot cast type integer to integer[]
LINE 1: ...values(10)) a(i) where i = any((select * from foo)::int[]);
^
pavel=#
On Tue, 21 Aug 2007 21:36:00 +0200
"Pavel Stehule" <pavel.stehule@gmail.com> wrote:
> it works? no.
>
> pavel=# select 1 from (values(10)) a(i) where i = any((select *
> from foo)::int[]);
> ERROR: cannot cast type integer to integer[]
> LINE 1: ...values(10)) a(i) where i = any((select * from
> foo)::int[]); ^
> pavel=#
oops - forgot to send to list:
This is the exact query I ran which produces a result set:
select * from tblproducts
where productid = ANY(
(select pb_correspondence from pb.pb_nurse
where id = 140)::integer[]
)
order by productid;
Your query looks similar - maybe it's the "select *" in the subquery?
Either way I actually found a better way to do what I was trying to
do here so no worries. :)
Josh
On Aug 21, 2007, at 14:35 , Josh Trutwin wrote: > it's a simple example but the number of list-type > fields is anticipated to be VERY high so I decided to deal with > arrays instead of creating all sorts of normalized tables. > Was it the right thing to do? No offense, but I doubt it. This doesn't seem to be a very good reason to move away from normal database practices. As I previously mentioned, you're throwing referential integrity out the window. Do things right unless you have a measurable reason to do things another way. Sounds like premature optimization to me, so all the normal rules of premature optimization apply. Michael Glaesemann grzm seespotcode net