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

"Moray McConnachie" <moray(dot)mcconnachie(at)computing-services(dot)oxford(dot)ac(dot)uk> writes:
> Agreed - but note that pg_dump currently produces CREATE INDEX
> statements with opclasses included.

Right, as it should since its purpose is to ensure you rebuild exactly
the same database. I was just opining that handwritten CREATE INDEXes
usually can omit the opclass. (BTW, I fixed the problem with functional
indexes needing an explicit opclass spec last night.)

> 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?

An embarrassing problem. We are going to work around this by having
the 7.0 parser discard the word "datetime" if it sees it in the opclass
position. There are a couple of other now-dead opclass names that will
be discarded in the same way. Klugy, but it will get the job done for
reading old dump files.

(This hack is not in 7.0beta1, but will be in beta2.)

regards, tom lane