From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@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.