Received: from oxmail.ox.ac.uk (oxmail2.ox.ac.uk [163.1.2.1]) by hub.org (8.9.3/8.9.3) with ESMTP id GAA02587 for ; Fri, 25 Feb 2000 06:16:16 -0500 (EST) (envelope-from moray.mcconnachie@computing-services.oxford.ac.uk) Received: from ermine.ox.ac.uk ([163.1.2.13]) by oxmail.ox.ac.uk with esmtp (Exim 2.10 #1) id 12OIjG-0000V1-00 for pgsql-sql@postgresql.org; Fri, 25 Feb 2000 11:16:14 +0000 Received: from ermine.ox.ac.uk ([163.1.2.13] helo=moraypc ident=root) by ermine.ox.ac.uk with smtp (Exim 3.13 #1) id 12OIjG-0000bS-00 for pgsql-sql@postgreSQL.org; Fri, 25 Feb 2000 11:16:14 +0000 Message-ID: <006e01bf7f81$b9a91d40$760e01a3@oucs.ox.ac.uk> From: "Moray McConnachie" To: References: <23337.951436670@sss.pgh.pa.us> Subject: Re: [SQL] how to create index on timestamp field in pre v7 database Date: Fri, 25 Feb 2000 11:16:10 -0000 Organization: Oxford University MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 5.00.2314.1300 X-MimeOLE: Produced By Microsoft MimeOLE V5.00.2314.1300 ---------------------------------------------------------------- Moray.McConnachie@computing-services.oxford.ac.uk ----- Original Message ----- From: Tom Lane To: Alex Howansky Cc: Sent: Thursday, February 24, 2000 11:57 PM Subject: Re: [SQL] how to create index on timestamp field in pre v7 database > Alex Howansky 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