[falter213]

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.

[Jacques1]

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).

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.

[falter213]

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