SEO

#1 (**permalink**) 04-12-2008, 02:31 AM

Jaime Casanova wrote:
> suppose we have something like this:
>
> upd_views=# create table tabla1 (col1 point);

[...]

> then, this select will give an error:
>
> upd_views=# select col1, count(*) from tabla1 group by col1;
> ERROR: could not identify an ordering operator for type point
> HINT: Use an explicit ordering operator or modify the query.
> upd_views=#
>
> i guess this is related to:
> http://archives.postgresql.org/pgsql...8/msg00809.php
>
> so, what happened with this idea? there is another way to automagicaly
> identify an "equality operator" for datatypes like 'point'?

I don't think that there is a connection to the message you quote.

In order to (efficiently) process a GROUP BY clause, you need a
total ordering on the data type that you group by, i.e. an ordering
such that for any two data x and y you have either x < y or x > x
or x = y.

There is no reasonable total ordering on the real plain, so you will
have to roll your own. How about:

select point(col1[0], col1[1]), count(*) from tabla1 group by col1[0],
col1[1];

Yours,
Laurenz Albe

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

#2 (**permalink**) 04-12-2008, 02:32 AM

On Mon, May 22, 2006 at 10:23:28 +0200,
Albe Laurenz <all@adv.magwien.gv.at> wrote:
>
> In order to (efficiently) process a GROUP BY clause, you need a
> total ordering on the data type that you group by, i.e. an ordering
> such that for any two data x and y you have either x < y or x > x
> or x = y.

An equality operator is good enough if the number of unique groups isn't too
large, so that a hash aggregate plan works efficiently.

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

#3 (**permalink**) 04-12-2008, 02:32 AM

Bruno Wolff III <bruno@wolff.to> writes:
> On Mon, May 22, 2006 at 10:23:28 +0200,
> Albe Laurenz <all@adv.magwien.gv.at> wrote:
>> In order to (efficiently) process a GROUP BY clause, you need a
>> total ordering on the data type that you group by, i.e. an ordering
>> such that for any two data x and y you have either x < y or x > x
>> or x = y.

> An equality operator is good enough if the number of unique groups isn't too
> large, so that a hash aggregate plan works efficiently.

Doesn't help for the case at hand, since point_eq isn't marked hashable
either. It would be good to fix things so that the system doesn't
insist on having the sorting option available, though.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

#4 (**permalink**) 04-12-2008, 02:32 AM

On Mon, May 22, 2006 at 18:38:35 -0400,
Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Bruno Wolff III <bruno@wolff.to> writes:
> > On Mon, May 22, 2006 at 10:23:28 +0200,
> > Albe Laurenz <all@adv.magwien.gv.at> wrote:
> >> In order to (efficiently) process a GROUP BY clause, you need a
> >> total ordering on the data type that you group by, i.e. an ordering
> >> such that for any two data x and y you have either x < y or x > x
> >> or x = y.
>
> > An equality operator is good enough if the number of unique groups isn't too
> > large, so that a hash aggregate plan works efficiently.
>
> Doesn't help for the case at hand, since point_eq isn't marked hashable
> either. It would be good to fix things so that the system doesn't
> insist on having the sorting option available, though.

Yeah, I thought about that later on my way home. You need to have a hash
function that maps equal values to the same hash bucket or things don't
work.

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SEO

Re: group by points