Postgres Pro
Downloads
Home   >   mailing lists

Thread: timestamp(0) vs. timestamp

timestamp(0) vs. timestamp

From
Erwin Brandstetter
Date:
Hi all!

This may seem unimportant, but I still would like to know.

I have columns for timestamps without fractional digits, so I could
define them as timestamp(0).
However, there is no way fractions could ever enter anyway, because
triggers and / or checks guarantee values without fractional seconds.

Is it advantageous to define the column as timestamp(0) or simply as
timestamp?
Does the query planner or any other part of the RDBMS profit from the
additional information in the definition?
Or do I just create an overhead of useless checks or conversions
(however small)?

In a scenario like that, what would be (however slightly) preferable:
CREATE TABLE x
(
   ...
   log_in timestamp(0) without time zone NOT NULL DEFAULT
(now())::timestamp without time zone
or
   log_in timestamp without time zone NOT NULL DEFAULT
(now())::timestamp(0) without time zone
or
   log_in timestamp(0) without time zone NOT NULL DEFAULT
(now())::timestamp(0) without time zone


TIA
Erwin Brandstetter

Re: timestamp(0) vs. timestamp

From
Tom Lane
Date:
Erwin Brandstetter <brsaweda@gmail.com> writes:
> Hi all!
> This may seem unimportant, but I still would like to know.

> I have columns for timestamps without fractional digits, so I could
> define them as timestamp(0).
> However, there is no way fractions could ever enter anyway, because
> triggers and / or checks guarantee values without fractional seconds.

> Is it advantageous to define the column as timestamp(0) or simply as
> timestamp?
> Does the query planner or any other part of the RDBMS profit from the
> additional information in the definition?
> Or do I just create an overhead of useless checks or conversions
> (however small)?

There's no advantage to the system from knowing that, but consider that
having timestamp_in force the values to be nonfractional is likely to be
faster than having logic in a trigger to do it.

            regards, tom lane

Re: timestamp(0) vs. timestamp

From
Erwin Brandstetter
Date:
On 27.04.2011 19:36, Tom Lane wrote:
> Erwin Brandstetter<brsaweda@gmail.com>  writes:
>> Hi all!
>> This may seem unimportant, but I still would like to know.
>> I have columns for timestamps without fractional digits, so I could
>> define them as timestamp(0).
>> However, there is no way fractions could ever enter anyway, because
>> triggers and / or checks guarantee values without fractional seconds.
>> Is it advantageous to define the column as timestamp(0) or simply as
>> timestamp?
>> Does the query planner or any other part of the RDBMS profit from the
>> additional information in the definition?
>> Or do I just create an overhead of useless checks or conversions
>> (however small)?
> There's no advantage to the system from knowing that, but consider that
> having timestamp_in force the values to be nonfractional is likely to be
> faster than having logic in a trigger to do it.
>
>             regards, tom lane



Thank you for the insight.
Is there any kind of overhead for timestamp_in due to the precision modifier - if non-fractional values are given?


Regards
Erwin Brandstetter