Обсуждение: Port Bug Report: Can't create indicies on type "timestamp"

============================================================================
                        POSTGRESQL BUG REPORT TEMPLATE
============================================================================


Your name               : Nathan Parker
Your email address      : nparker@pobox.com

Category                : runtime: back-end
Severity                : non-critical

Summary: Can't create indicies on type "timestamp"

System Configuration
--------------------
  Operating System   : Linux 2.0.36 RedHat

  PostgreSQL version : 6.5

  Compiler used      : gcc 2.8.1

Hardware:
---------
Linux sprocket.beast.com 2.0.36 #10 Mon Mar 1 05:23:53 MST 1999 i486 unknown

Versions of other tools:
------------------------


--------------------------------------------------------------------------

Problem Description:
--------------------
Whenever I try to create an index that includes a "timestamp"
field, I get this error:
ERROR:  Can't find a default operator class for type 1296.

This prevents me from using the timestamp type altogether.
Big bummer.  I like PostgreSQL, but I'm thinking of
switching to Sybase's free Linux server.  Thanks for all
the good work.

--------------------------------------------------------------------------

Test Case:
----------
% createdb foo
% psql foo
Welcome to the POSTGRESQL interactive sql monitor:
  Please read the file COPYRIGHT for copyright terms of POSTGRESQL
[PostgreSQL 6.5.0 on i486-pc-linux-gnu, compiled by gcc 2.8.1]

   type \? for help on slash commands
   type \q to quit
   type \g or terminate with semicolon to execute query
 You are currently connected to the database: foo

foo=> create table foo1 (ts timestamp);
CREATE
foo=> create index foo1_idx on foo1 (ts);
ERROR:  Can't find a default operator class for type 1296.
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^


--------------------------------------------------------------------------

Solution:
---------


--------------------------------------------------------------------------

>   PostgreSQL version : 6.5
>
>   Compiler used      : gcc 2.8.1
>
> Problem Description:
> --------------------
> Whenever I try to create an index that includes a "timestamp"
> field, I get this error:
> ERROR:  Can't find a default operator class for type 1296.
>
> This prevents me from using the timestamp type altogether.
> Big bummer.  I like PostgreSQL, but I'm thinking of
> switching to Sybase's free Linux server.  Thanks for all
> the good work.
>
> --------------------------------------------------------------------------
>
>
> foo=> create table foo1 (ts timestamp);
> CREATE
> foo=> create index foo1_idx on foo1 (ts);
> ERROR:  Can't find a default operator class for type 1296.
> ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

I can confirm this as a bug.

--
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@candle.pha.pa.us            |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

> Summary: Can't create indicies on type "timestamp"
> Problem Description:
> --------------------
> Whenever I try to create an index that includes a "timestamp"
> field, I get this error:
> ERROR:  Can't find a default operator class for type 1296.
> This prevents me from using the timestamp type altogether.
> Big bummer.  I like PostgreSQL, but I'm thinking of
> switching to Sybase's free Linux server.

*shrug* Go fer it dude! But you might want to RTFM before switching ;)

> foo=> create table foo1 (ts timestamp);
> CREATE
> foo=> create index foo1_idx on foo1 (ts);
> ERROR:  Can't find a default operator class for type 1296.
> ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

The current timestamp type is *not* well supported. You should be
using the "datetime" type, which will become the "timestamp" type
sometime soon. In fact, it should probably happen for the next
release. I'd been waiting for a *major* release to make this switch,
but it doesn't seem worth the wait.

Check the chapter on "Data Types" in the User's Guide for pointers on
the date/time types.

Good luck.

                    - Thomas

--
Thomas Lockhart                lockhart@alumni.caltech.edu
South Pasadena, California