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

Re: [SQL] how to create index on timestamp field in pre v7 database

From: "Moray McConnachie" <moray(dot)mcconnachie(at)computing-services(dot)oxford(dot)ac(dot)uk>
To: <pgsql-sql(at)postgresql(dot)org>
Subject: Re: [SQL] how to create index on timestamp field in pre v7 database
Date: 2000-02-25 11:16:10
Message-ID: 006e01bf7f81$b9a91d40$760e01a3@oucs.ox.ac.uk (view raw or flat)
Thread:
Lists: pgsql-hackerspgsql-sql
----------------------------------------------------------------
Moray(dot)McConnachie(at)computing-services(dot)oxford(dot)ac(dot)uk
----- Original Message -----
From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Alex Howansky <alex(at)wankwood(dot)com>
Cc: <pgsql-sql(at)postgreSQL(dot)org>
Sent: Thursday, February 24, 2000 11:57 PM
Subject: Re: [SQL] how to create index on timestamp field in pre v7
database


> Alex Howansky <alex(at)wankwood(dot)com> writes:
> > My question is, how "equivalent" are these types?
>
> They're the same code: we jacked up the name "timestamp" and rolled
the
> old datetime code underneath.  Strictly a matter of coming closer to
> the SQL standard names for these datatypes.
>
> > Can I use datetime_ops to index a timestamp field in a v6.5.3
database?
>
> Similarly, "datetime_ops" in 6.5 is now "timestamp_ops".
>
> As a rule, I'd suggest not bothering with opclasses in index
> declarations.  The only situation where you need to select one is
> where there is more than one possible opclass for the same datatype.
> This holds for some of the geometric types, but not for any plain
scalar
> types like numerics or date/time types.  (You could think of an
opclass
> as specifying which sort order the index uses...)

Agreed - but note that pg_dump currently produces CREATE INDEX
statements with opclasses included.

That means running a script created by pg_dump v.6.5.x will fail under
7.0 because there is no index opclass of the type datetime?

Judging from my brief experiments that looks to be the case, anyway.
It looks like replacing all occurrences of datetime with timestamp in
the script works - even for the few functions I have that used the
datetime() function.

Yours,
Moray



In response to

Responses

pgsql-hackers by date

Next:From: Andrzej MazurkiewiczDate: 2000-02-25 13:31:15
Subject: [HACKERS] Re: Interested in writing a PostgreSQL article?
Previous:From: Michael ReifenbergerDate: 2000-02-25 09:50:58
Subject: failing to compile -current on FreeBSD

pgsql-sql by date

Next:From: NeduDate: 2000-02-25 13:54:13
Subject: Problem - Postgres Running continously
Previous:From: PostmasterDate: 2000-02-25 05:48:29
Subject: DELIVERY FAILURE: User laszlo_acs (laszlo_acs@cms.state.il.us) not listed in public Name & Address Book

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