Re: why doesn't an index help my simple query?

At 11:23 PM -0400 5/30/03, Tom Lane wrote:
>Peter Bierman <bierman(at)apple(dot)com> writes:
>> At 10:29 PM -0400 5/30/03, Tom Lane wrote:
>>> Hm, why is that shown as a "filter" and not an "index condition"? And
>>> why is there an explicit conversion to timestamp with time zone in
>>> there? Better tell us about the exact data types involved here ...
>
>> I was hoping you'd say 'hm'. :-)
>
>> CREATE TABLE events (
>> "time" timestamp without time zone DEFAULT
>> ('now'::text)::timestamp(6) with time zone NOT NULL,
>
>Right. You're getting bit by ye same olde problem of datatype mismatch:
>the planner does not realize that there is any connection between the
>types "timestamp without time zone" and "timestamp with time zone", so
>the presence of a WHERE condition expressed in terms of a timestamp-with-
>tz operator doesn't induce it to do anything that a timestamp-without-tz
>index could recognize.
>
>Short answer is you probably ought to declare events.time as timestamp
>with time zone; or if you have a *really good* reason why it should not
>be declared that way, you ought to cast what you are comparing it to
>to timestamp without tz.

Ok, two more questions then:

1) is there an easy way for me to change the type in place?

2) I created the table initially with:

CREATE TABLE events
(
time TIMESTAMP UNIQUE NOT NULL DEFAULT CURRENT_TIMESTAMP,

Should the plain 'timestamp' type really default to a different type
than what all the rest of the timestamp operators prefer?

Thanks!

-pmb