Thanks a lot David, You are right, I want to left to be a superset (or equal at the limit) of the right set, but taking cardinalities of elements into account. Please do not spend time on this, since it appears I can't keep the indexing advantage of array operation under these circumstances.
Actually, the sets are created via concatenating ids that represent node types in a tree. I wanted to develop a solution using arrays, but maybe I should look into ltree, which may help me do what I want to do without losing the advantage of indexing. Or I can implement some other logic in the business layer that ensures that duplicate elements are eliminated before inserts and queries with a replacement strategy that changes second and further occurrences of node ids on both left and right side of the array operation. This may help me keep array based approach. I am not sure how ltree would scale in terms of performance to a few billion rows. Best regards Seref On Wed, Aug 8, 2012 at 3:45 PM, David Johnston <pol...@yahoo.com> wrote: > From: pgsql-general-ow...@postgresql.org > [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Seref Arikan > Sent: Wednesday, August 08, 2012 10:35 AM > To: PG-General Mailing List > Subject: [GENERAL] 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. > > >
