#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2013
    Posts
    2
    Rep Power
    0

    [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.
  2. #2
  3. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,957
    Rep Power
    1046
    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.
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2013
    Posts
    2
    Rep Power
    0
    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

IMN logo majestic logo threadwatch logo seochat tools logo