From: pgsql-general-owner@xxxxxxxxxxxxxx [mailto:pgsql-general-owner@xxxxxxxxxxxxxx] On Behalf Of Seref Arikan Sent: Wednesday, August 08, 2012 10:35 AM To: PG-General Mailing List Subject: Using contains operator on arrays that have duplicate elements: feedback request. Greetings, I've been trying to use contains operator on an array such as {3,4,9,4,5} My problem is, I'd like to get rows that has two 4s in them, and contains operator seems to work separately for each member of the right operand. Therefore select '{3,4,9,4,5}'::int[] @> '{4,4,4}' returns true. I'm trying to implement the semantics of the operation so that the set {4,4,4} is a single parameter to a subset query, which should return false in the case above. Is there any way I can force the contains operation to consider cardinality of unique members of the set ? Arrays look like to simplest way of performing set operations on a group of values, but is there anything else that can be an alternative to them? Best regards Seref ============================================================ The only thing I can think of is to create a function that unnest(s) the two supplied arrays, groups on the value and performs a count, then LEFT JOINs the two grouped relations - limited by a count comparison - and returns TRUE/FALSE depending on whether there are any NULLs on the right side of the join. I may try a stab and an example of this later if it is still an open question but hopefully this will help you. I do not believe you are going to be able to change the behavior of "contains" in the way that you want. You might also try performing an intersection and then confirm that the result is the same as the compare-to array; it seems that you want the left-side to be a super-set of the right-side in the example provided. Apologies if I get some of the left/right/compare-to terminology mixed-up. I hope that at least the intent is clear. HTH David J. -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
