Thread: temporal period type and select distinct gives equality error

temporal period type and select distinct gives equality error

From
Arturo Perez
Date:
Hi all,

I'm using the temporal contrib package that creates the period type and
a bunch of operators in postgresql 8.4.5.  I plan to use the same code
against 9.0 so if anything differs between the two regarding this issue
please let me know.

In psql the \do command shows this
 public | =    | period | period  | boolean  |

and yet when I do a select distinct with a join I get

QueryLogger - *** error.
org.postgresql.util.PSQLException: ERROR: could not identify an equality
operator for type period

If I leave the period column out of the select list then it works fine.

Did I install it wrong?  Or is something else going on?

tia
arturo

Re: temporal period type and select distinct gives equality error

From
Arturo Perez
Date:
In article <arturo-26A95E.00305123012011@news.gmane.org>,
 arturo@pleeque.com (Arturo Perez) wrote:

> Hi all,
>
> I'm using the temporal contrib package that creates the period type and
> a bunch of operators in postgresql 8.4.5.  I plan to use the same code
> against 9.0 so if anything differs between the two regarding this issue
> please let me know.
>
> In psql the \do command shows this
>  public | =    | period | period  | boolean  |
>
> and yet when I do a select distinct with a join I get
>
> QueryLogger - *** error.
> org.postgresql.util.PSQLException: ERROR: could not identify an equality
> operator for type period
>
> If I leave the period column out of the select list then it works fine.
>
> Did I install it wrong?  Or is something else going on?
>
> tia
> arturo

No suggestions from anyone?  Anyone out there? :-)

-arturo

Re: temporal period type and select distinct gives equality error

From
Jeff Davis
Date:
On Wed, 2011-01-26 at 09:27 -0500, Arturo Perez wrote:
> > and yet when I do a select distinct with a join I get
> >
> > QueryLogger - *** error.
> > org.postgresql.util.PSQLException: ERROR: could not identify an equality
> > operator for type period
> >
> > If I leave the period column out of the select list then it works fine.
> >
> > Did I install it wrong?  Or is something else going on?
> >
> > tia
> > arturo
>
> No suggestions from anyone?  Anyone out there? :-)

Sorry, I missed this the first time.

I have added support for this in the CVS repository, but have not
included it in the latest release. The thing that's missing from the
released files is a "btree opclass", which is what postgresql uses to
find the right equality operator for DISTINCT.

I am currently working on "range types" which is a patch that will
provide support for PERIOD, as well as many other range types, in core.
That will, of course, support everything you expect, including DISTINCT
on the range type.

We're currently in the middle of the final commit fest for 9.1. After
this is over (or perhaps earlier, if I can find the time), I'll do some
clean up of the existing pgsql-temporal PERIOD type, and do a release of
that.

Regards,
    Jeff Davis


Re: temporal period type and select distinct gives equality error

From
Arturo Perez
Date:
In article <1296066333.11513.364.camel@jdavis>,
 Jeff Davis <pgsql@j-davis.com> wrote:

> On Wed, 2011-01-26 at 09:27 -0500, Arturo Perez wrote:
> > > and yet when I do a select distinct with a join I get
> > >
> > > QueryLogger - *** error.
> > > org.postgresql.util.PSQLException: ERROR: could not identify an equality
> > > operator for type period
> > >
> > > If I leave the period column out of the select list then it works fine.
> > >
> > > Did I install it wrong?  Or is something else going on?
> > >
> > > tia
> > > arturo
> >
> > No suggestions from anyone?  Anyone out there? :-)
>
> Sorry, I missed this the first time.
>
> I have added support for this in the CVS repository, but have not
> included it in the latest release. The thing that's missing from the
> released files is a "btree opclass", which is what postgresql uses to
> find the right equality operator for DISTINCT.


I thought I saw that in CVS but when I checked it out and installed it
the error did not go away.

Let me try that again.  Do you think I'd need to reinstall the server
itself to insure the proper behavior?

tia,
arturo

Re: temporal period type and select distinct gives equality error

From
Jeff Davis
Date:
On Thu, 2011-01-27 at 07:32 -0500, Arturo Perez wrote:
> I thought I saw that in CVS but when I checked it out and installed it
> the error did not go away.
>
> Let me try that again.  Do you think I'd need to reinstall the server
> itself to insure the proper behavior?

No, reinstalling the extension should fix it.

Also, there is a mailing list for this extension as well:
http://lists.pgfoundry.org/pipermail/temporal-general/

Regards,
    Jeff Davis