Re: order by, for custom types - Mailing list pgsql-hackers

Take the query.

select a,b from dupa where b::text in (select b::text from dupa group by 
b::text having count(b) > 2);

This is acceptable to create a unique constraint, however, we cannot mark 
the column unique, without defining btree operators, which clearly are not 
possible for sorting. Is there any way to base the operators based on the 
text representation of the type for strict equality (not to be confused with 
same or equivilent) and thus use that not as an ordering method, but as a 
simple equality for uniqueness.

Kevin McArthur

----- Original Message ----- 
From: "Andrew - Supernews" <andrew+nonews@supernews.com>
To: <pgsql-hackers@postgresql.org>
Sent: Saturday, November 19, 2005 10:54 PM
Subject: Re: [HACKERS] order by, for custom types


> On 2005-11-19, Grzegorz Jaskiewicz <gj@pointblue.com.pl> wrote:
>> Wildcards cause things not to work as they should
>>
>> consider everything in [] brackets to be a possible choice and those
>> three:
>>
>> a = 1.2.3.4
>> b = 1.[2,3].3.4
>> c = 1.3.3.4
>>
>> a = b, b = c, but a <> c, I was told that because of that btree won't
>> work on my type. (on irc, that was AndrewSN as I recall).
>
> Probably. But nothing stops you defining equality and ordering operators
> that _do_ work for btree, and hence sorting, it's just that those 
> operators
> won't be any use for the matching semantics.
>
> It's clear that for your data type that there is a concept of "equality"
> in which all three of your values a,b,c above are unequal. My advice would
> be (and I'm sure I suggested this at the time) that you reserve the '='
> operator for a true equality operation, and use some other operator such 
> as
> ~ or @ for the "matches" semantics that you want for your application.
> Having an intransitive '=' operator violates the POLA, even if it doesn't
> actively break anything otherwise (I have no idea if it does).
>
>> I do have all operators required for btree, no operator class
>> defined, every single operator. Btree requires some function apart
>> from operators, this one is not defined, but I do have = operator as
>> well.
>
> You still don't seem to understand that what btree requires is not an
> operator _called_ '=', but an operator with the logical semantics of
> "equality". That operator can be called anything you please (it doesn't
> have to have the name '=').
>
> Sorting doesn't need an equality operator, since it can fabricate one if
> given a suitable < operator, i.e. one that constitutes a strict weak
> ordering over the elements to be sorted; it can rely on the fact that
> NOT(a < b) AND NOT(b < a) implies that a and b are equivalent for sorting
> purposes. (The requirement that < constitute a strict weak ordering is
> enough to ensure that this is an equivalence relation, and therefore
> transitive; if < does not meet this requirement then sorting may give 
> wrong
> answers, loop forever, or possibly crash.)
>
> -- 
> Andrew, Supernews
> http://www.supernews.com - individual and corporate NNTP services
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster
>