Thread: Comparing arrays

Hello,

Id like to compare two arrays, and if any value is found in both, return
true.  Is this possible without needing a procedure to loop through one?

For example, say categories is an array :

SELECT * FROM t1 WHERE ANY(ARRAY[5,7,9]) = ANY(categories);

Obviously this doesn't work, but you can kinda see what Im trying to do.

Thank you for any insight,
- A Gilmore

A Gilmore <agilmore@shaw.ca> schrieb:

> Hello,
>
> Id like to compare two arrays, and if any value is found in both, return
> true.  Is this possible without needing a procedure to loop through one?
>
> For example, say categories is an array :
>
> SELECT * FROM t1 WHERE ANY(ARRAY[5,7,9]) = ANY(categories);

Nice question. I have written a little function. You can see this on
http://a-kretschmer.de/tools/array_compare.sql.
Let me know if you have a better solution.


Regards, Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect. (Linus Torvalds)
Kaufbach, Saxony, Germany, Europe.              N 51.05082°, E 13.56889°

Andreas Kretschmer wrote:
> A Gilmore <agilmore@shaw.ca> schrieb:
>
>
>>Hello,
>>
>>Id like to compare two arrays, and if any value is found in both, return
>>true.  Is this possible without needing a procedure to loop through one?
>>
>>For example, say categories is an array :
>>
>>SELECT * FROM t1 WHERE ANY(ARRAY[5,7,9]) = ANY(categories);
>
>
> Nice question. I have written a little function. You can see this on
> http://a-kretschmer.de/tools/array_compare.sql.
> Let me know if you have a better solution.
>
>
> Regards, Andreas

Thank you for the reference, the use of array_upper() is quite useful.
Apparently there is no straight-forward way to do it in SQL, so I ended
up writing a similiar plpgsql function.

- A Gilmore