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" <[EMAIL PROTECTED]>
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 <[EMAIL PROTECTED]> 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
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
