CODEWITHSUNDEEP
postgresqldefault-value
Incognito
Incognito

Reputation: 20765

Enforcing default time when only date in timestamptz provided

Assume I have the table:

postgres=# create table foo (datetimes timestamptz);
CREATE TABLE
postgres=# \d+ foo 
                            Table "public.foo"
  Column   |           Type           | Modifiers | Storage | Description 
-----------+--------------------------+-----------+---------+-------------
 datetimes | timestamp with time zone |           | plain   | 
Has OIDs: no

So lets insert some values into it... 

postgres=# insert into foo values 
    ('2012-12-12'), --This is the value I want to catch for.
    (null), 
    ('2012-12-12 12:12:12'), 
    ('2012-12-12 12:12');
INSERT 0 4

And here's what we have:

postgres=# select * from foo ;
       datetimes        
------------------------
 2012-12-12 00:00:00+00

 2012-12-12 12:12:12+00
 2012-12-12 12:12:00+00
(4 rows)

Ideally, I'd like to set up a default time-stamp value when a TIME is not provided with the input, rather than the de-facto time of 2012-12-12 being 00:00:00, I would like to set a default of 15:45:10.

Meaning, my results should look like:

postgres=# select * from foo ;
       datetimes        
------------------------
 2012-12-12 15:45:10+00    --This one gets the default time.

 2012-12-12 12:12:12+00
 2012-12-12 12:12:00+00
(4 rows)

I'm not really sure how to do this in postgres 8.4, I can't find anything in the datetime section of the manual or the sections regarding column default values.

Upvotes: 3

Views: 800

Answers (1)

Daniel Vérité
Daniel Vérité

Reputation: 61526

Values for new rows can be tweaked in a BEFORE INSERT trigger. Such a trigger could test if there's a non-zero time component in NEW.datetimes, and if not set it to the desired fixed time.

However, the case when the time part is explicitly set to zero in the INSERT clause cannot be handled with this technique because '2012-12-12'::timestamptz is equal to '2012-12-12 00:00:00'::timestamptz. So it would be as trying to distinguish 0.0 from 0.00.

Technically, tweaking the value should happen before the implicit cast from string to the column's type, which even a RULE (dynamic query rewriting) cannot do.

It seems to me that the best option is to rewrite the INSERT and apply a function to each value converting it explicitly from string to timestamp. This function would test the input format and add the time part when needed:

  create function conv(text) returns timestamptz as $$
    select case when length($1)=10 then ($1||' 15:45:10')::timestamptz
      else $1::timestamptz end; $$
   language sql strict immutable;

insert into foo values 
    (conv('2012-12-12')),
    (conv(null)), 
    (conv('2012-12-12 12:12:12')), 
    (conv('2012-12-12 12:12'));

Upvotes: 3

Related Questions