Zeljko Vrba wrote:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
Hi! I have posted this as a user comment in section 9.17:
- ----
= ANY(array expression) doesn't work in the obvious way when the array
expression is a subselect. For example:
select * from stat3 where stat3.id = any ('{4,5,6,7}');
works (and returns the expected tuples). However,
select * from stat3 where stat3.id = any (select stat3 from
helix_request where id=11);
DOESN'T work and complains with an error: operator does not exist:
integer = integer[]. The inner select returns EXACTLY ONE value, namely
the same array as the literal in the first example.
No, it doesn't - hence the error.
What it returns is a SET containing ONE TUPLE which contains ONE ARRAY.
Here the "any" ranges over the SET not the array, so it tries to compare
the outer integer with the inner array of integers.
The solution is:
select * from stat3 where (select stat3.id = any (stat3) from
helix_request where id=11);
I thank to Chris Kings-Lynne ("KL") for helping me out with this over IRC.
- ----
KL suggested to mail this question to the hackers list. The problem with
this solution is that postgresql uses sequential scan for the proposed
solution:
[snip]
I'm going to have MANY queries of this kind and having sequential scan
for each such query will lead to quadratic performance - unacceptable
for the amount of data I will have (millions of rows).
Is there yet another way of making WHERE field = ANY (subselect
returning an array) work? Or make postgres to use index?
OK, I know the suggestion from the manual: usually it's bad database
design searching through arrays and a separate table is better. however,
to convert {4,5,6,7} into a table would require a table with two
columns, e.g. (1,4), (1,5), (1,6), (1,7) where the first column would be
used to identify elements in the single array). this additional column
is a waste of space.
You're trading space for time, and deliberately spoiling your design to
do so. If you're going to want to access each value separately, design
your system to reflect that fact.
Have you actually tested your example with a proper two-column table? It
might be that weaknesses in PostgreSQL force you to compromise your
design, but I wouldn't start from that point. Try a proper relational
design, with a bit of tuning and see if that will work for you.
If you really want to use arrays, perhaps see if the contrib/intarray
module can help you.
--
Richard Huxton
Archonet Ltd
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
