postgresql - 当仅提供 timestamptz 中的日期时强制执行默认时间

Question

假设我有表:

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

所以让我们向其中插入一些值......

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

这就是我们所拥有的:

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)

理想情况下，我想在输入未提供 TIME 时设置默认时间戳值，而不是 2012-12-12 的实际时间 00:00:00，我想设置默认值 15:45:10。

意思是，我的结果应该如下所示:

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)

我不太确定如何在 postgres 8.4 中执行此操作，我在 datetime 中找不到任何内容手册部分或有关列默认值的部分。

Answer 1

可以在 BEFORE INSERT 触发器中调整新行的值。这样的触发器可以测试 NEW.datetimes 中是否有非零时间组件，如果没有则将其设置为所需的固定时间。

但是，无法使用此技术处理 INSERT 子句中时间部分显式设置为零的情况，因为 '2012-12-12'::timestamptz 等于 '2012-12-12 00:00:00'::timestamptz。所以这就像试图区分 0.0 和 0.00。

从技术上讲，调整值应该发生在从字符串到列类型的隐式转换之前，即使是 RULE(动态查询重写)也做不到。

在我看来，最好的选择是重写 INSERT 并对每个值应用一个函数，将它显式地从字符串转换为时间戳。此函数将测试输入格式并在需要时添加时间部分:

  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'));