January 29th, 2013, 05:57 PM
-
[Solved] How to write the SQL for: WHERE integer IN integer[] ??
First off, please excuse my ignorance. I am familiar with SQL but not fluent.
I want to select all rows whose integer ids are listed in another row's dependent_on integer[]. I tried many permutations on this last night and was not able to get it to work.
In my own head, this is how the SQL should read:
SELECT description FROM public.cmdb WHERE id IN (SELECT dependent_on FROM public.cmdb WHERE description = 'mydescription');
But no, this gives me:
ERROR: operator does not exist: integer = integer[]
I don't understand why it thinks I'm doing an = ... I want to SELECT WHERE id is IN the array.
January 29th, 2013, 06:18 PM
-
Hi,
using IN with a subselect checks whether the value(s) occur in the listed column(s). So it's not an array operation.
What you want is the ANY expression together with the "is contained by" operator (I'm not aware of an "is element of" operator).
Code:
SELECT
description
FROM
cmbd
WHERE
ARRAY[id] <@ ANY (
SELECT
dependent_on
FROM
cmbd
WHERE
description = 'mydescription'
)
However, you should really consider to normalize your database and get rid of those arrays. They're are horrible with regard to efficiency, usability and data integrity. They basically circumvent the whole relational logic.
If you do that, your queries will get much easier and more straightforward.
January 29th, 2013, 07:03 PM
-
Ah, yes that works perfectly. I'd never seen that notation, but I understand your explanation.
I will reconsider the structure and remove that array at the first major maintenance opportunity.
Thank you so much