Search Postgresql Archives

Re: Using contains operator on arrays that have duplicate elements: feedback request.

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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



[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux