Skip site navigation (1) Skip section navigation (2)

Re: Creating a VIEW with a POINT column

From: Jan Urbański <j(dot)urbanski(at)students(dot)mimuw(dot)edu(dot)pl>
To: Mark Mielke <mark(at)mark(dot)mielke(dot)cc>
Cc: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, Nick <nboutelier(at)hotmail(dot)com>, pgsql-hackers(at)postgresql(dot)org
Subject: Re: Creating a VIEW with a POINT column
Date: 2008-06-26 01:14:10
Message-ID: 4862ED62.90101@students.mimuw.edu.pl (view raw or flat)
Thread:
Lists: pgsql-hackers
Mark Mielke wrote:
> Tom Lane wrote:
>> Type point has no btree opclass, no hash opclass, and not even an
>> operator named "=" (it looks like the functionality is named ~=
>> for some odd reason).  I'd be interested to hear either a proposal of
>> a principled way to define DISTINCT, or a way to implement it that
>> was better than comparing every element to every other element...

The way I see it there's nothing wrong with the definition of DISTINCT 
and for types that can't be compared there is no way of calculating 
distinct values other than comparing every element to every other.
My point is that it is theoretically possible to do DISTINCT with only a 
equality operator. Sure, it's impractical, but it's a valid operation. 
If you can tell which elements are equal, you can take the largest 
subset of elements, among which no two are equal.
The least that can be done is improve the error message. Maybe something 
like: "The query required to sort elements of type <foo> to calculate 
the result efficiently, but there is no ordering operator for type 
<foo>" would do. And document, that GROUP BY, DISTINCT and UNION fail on 
types that can't be sorted.

> I agree - a byte-wise comparison of the internal encoding might be 
> inadequate (compare "0.0e+1" to "0.0e+2" is "not equal" for instance?). 
> If the poster is referring to a translation to string before comparing, 

> The problem here seems to that "point" should have an equality operator?

I think it has (=~, as Tom pointed out). The real problem is: should 
there be code to do GROUP BY / DISTINCT when there are no btree or hash 
opclasses, or should it be considered an error, because doing it would 
take very long for larger result sets?

Cheers,
Jan

-- 
Jan Urbanski
GPG key ID: E583D7D2

ouden estin

In response to

Responses

pgsql-hackers by date

Next:From: Gregory WilliamsonDate: 2008-06-26 01:34:22
Subject: Re: Creating a VIEW with a POINT column
Previous:From: Tom LaneDate: 2008-06-26 01:04:07
Subject: Re: CVS Head psql bug?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group