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...)

regards, tom lane

PS: Actually there's a second case where you must specify an opclass,
which is if you are creating a functional index; for some reason the
system can't figure out the right opclass in that case. This is a bug,
no doubt ... never looked at it hard enough to see why it's failing.