Enforcing default time when only date in timestamptz provided
- by Incognito
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.
