Thread: SELECT and Arrays
<nl>
Hi all -
<nl><nl>
Trying to utilize an array within a table, and to SELECT it using the format [x:y]=something, but cant get it to
work...
<nl><nl>
Here is what I'm currently doing (the dumb way):
<nl><nl>
SELECT * FROM students WHERE testscore[1]>70 AND testscore[2]>70;
<nl><nl>
Here is what I'd like to do (the less dumb way;) ):
<nl><nl>
SELECT * FROM students WHERE testscore[1:2]>'{70,70}';
<nl><nl>
--------
<nl><nl>
However, PG keeps complaining about not finding an operator for types '_int4' and 'unknown'.
<nl><nl>
Anyone have any pointers??
<nl><nl>
Thanks,
Barry S
The two ways that you describe of querying are not (necessarily)equivalent, though.
From the first example you give, I assume that you are trying to find all rows where each element in the array is above a certain threshold. However, in the second query, what does [x:x] > [y:y] mean? Are all elements in the target supposed to be less than the threshold, or just any of them, or perhaps the mod (distance of the point from the origin) of the first is greater than the mod of the second?
Which is why there is no > operator in more than one dimension. It's a bit like saying: is (23,5) less than (45,2), using points from 2-space (x-y co-ordinates).
However, you could write an operator specifically for your case (all values on one side less than all values on the other). It's not difficult, if you feel like getting into the array package in contrib/ and/or don't mind doing some coding ;-), and the contrib/ code would probably provide some code closely matching what you want.
Cheers...
MikeA
-----Original Message-----
From: Barry Stinson
To: pgsql-general@postgresql.org
Sent: 13/03/01 23:08
Subject: [GENERAL] SELECT and Arrays
<nl>
Hi all -
<nl><nl>
Trying to utilize an array within a table, and to SELECT it using the
format [x:y]=something, but cant get it to work...
<nl><nl>
Here is what I'm currently doing (the dumb way):
<nl><nl>
SELECT * FROM students WHERE testscore[1]>70 AND testscore[2]>70;
<nl><nl>
Here is what I'd like to do (the less dumb way;) ):
<nl><nl>
SELECT * FROM students WHERE testscore[1:2]>'{70,70}';
<nl><nl>
--------
<nl><nl>
However, PG keeps complaining about not finding an operator for types
'_int4' and 'unknown'.
<nl><nl>
Anyone have any pointers??
<nl><nl>
Thanks,
Barry S
_________________________________________________________________________
This e-mail and any attachments are confidential and may also be privileged and/or copyright
material of Intec Telecom Systems PLC (or its affiliated companies). If you are not an
intended or authorised recipient of this e-mail or have received it in error, please delete
it immediately and notify the sender by e-mail. In such a case, reading, reproducing,
printing or further dissemination of this e-mail is strictly prohibited and may be unlawful.
Intec Telecom Systems PLC. does not represent or warrant that an attachment hereto is free
from computer viruses or other defects. The opinions expressed in this e-mail and any
attachments may be those of the author and are not necessarily those of Intec Telecom
Systems PLC.
This footnote also confirms that this email message has been swept by
MIMEsweeper for the presence of computer viruses.
__________________________________________________________________________