postgresql - postgres 默认值适用于更新，而不仅仅是创建

Question

在写这个问题的过程中，我找到了答案，会贴在下面。如果这个问题已经有重复项，请通知我，我会删除它，我找不到任何问题。

我有两列跟踪对 postgres 中的表所做的更改:

created_at timestamp default now()
updated_at timestamp

updated_at 列正在被触发器更新:

united_states_congress=> \d congressional_bill_summaries;
                                      Table "public.congressional_bill_summaries"
   Column    |            Type             |                                 Modifiers                                 
-------------+-----------------------------+---------------------------------------------------------------------------
 id          | bigint                      | not null default nextval('congressional_bill_summaries_id_seq'::regclass)
 text        | text                        | 
 created_at  | timestamp without time zone | default now()
 updated_at  | timestamp without time zone | 
 bill_kid    | integer                     | not null
 date        | date                        | not null
 description | character varying(255)      | not null
 text_hash   | uuid                        | 
Indexes:
    "congressional_bill_summaries_pkey" PRIMARY KEY, btree (id)
    "congressional_bill_summaries_bill_kid_date_description_key" UNIQUE CONSTRAINT, btree (bill_kid, date, description)
Triggers:
    hash_all_the_things BEFORE INSERT ON congressional_bill_summaries FOR EACH ROW EXECUTE PROCEDURE hash_this_foo()
    update_iz_yoo BEFORE UPDATE ON congressional_bill_summaries FOR EACH ROW EXECUTE PROCEDURE update_iz_now()

与表的另一列一样，text_hash

我的预期行为是，当第一次插入一行时，created_at 列将更新为默认值(我理解为当前事务开始的时间，而不是特定查询的时间)。

我预期的行为是，当一行更新时，updated_at 行将被这个函数更新:

CREATE OR REPLACE FUNCTION public.update_iz_now()
 RETURNS trigger
 LANGUAGE plpgsql
AS $function$
BEGIN
  NEW.updated_at = now();
  RETURN NEW;
END;
$function$

但是 created_at 将保持不变，因为列中已经有一个值，所以它不应该被默认值覆盖。

created_at 最初运行正常:

united_states_congress=> select created_at, updated_at from congressional_bill_actions limit 5;
         created_at         | updated_at 
----------------------------+------------
 2017-01-28 00:08:11.238773 | 
 2017-01-28 00:08:11.255533 | 
 2017-01-28 00:08:15.036168 | 
 2017-01-28 00:08:15.047991 | 
 2017-01-28 00:08:15.071715 | 
(5 rows)

但是当更新一行时，created_at 被更改以匹配 updated_at 的插入值，给我留下:

united_states_congress=> select created_at, updated_at from congressional_bill_actions where updated_at is not null limit 5;
         created_at         |         updated_at         
----------------------------+----------------------------
 2017-01-28 07:55:34.078783 | 2017-01-28 07:55:34.078783
 2017-02-01 18:47:50.673996 | 2017-02-01 18:47:50.673996
 2017-02-02 14:50:33.066341 | 2017-02-02 14:50:33.066341
 2017-02-02 14:50:33.083343 | 2017-02-02 14:50:33.083343
 2017-02-03 13:58:34.950716 | 2017-02-03 13:58:34.950716
(5 rows)

我一直在互联网上试图解决这个问题，但互联网一直在帮助我解决有关“如何创建默认值”和“如何创建触发器”的问题。

这显然是我这边某处的使用问题，但我无法识别它。为了以防万一，这是在表上运行的另一个触发器(插入时):

CREATE OR REPLACE FUNCTION public.hash_this_foo()
 RETURNS trigger
 LANGUAGE plpgsql
AS $function$
BEGIN
  NEW.text_hash = md5(NEW.text)::uuid;
  RETURN NEW;
END;
$function$

在写这个问题的过程中，我找到了答案，会贴在下面。如果这个问题已经有重复项，请通知我，我会删除它，我找不到任何问题。

Answer 1

这里的问题是我的 UPSERT 处理，在此期间，表的模式被拉入，导致动态创建包含如下行的查询:

ON CONFLICT ON CONSTRAINT congressional_bill_actions_bill_kid_date_action_actor_key DO UPDATE SET created_at = EXCLUDED.created_at, 

因为 created_at 被自动设置为 EXCLUDED.created_at，这导致默认值准确地覆盖现有值，因为我指示它这样做。

因此，在编写 UPSERT 处理程序时，这似乎是需要注意的事情。

(注意:避免这种情况的方法就是不拉入任何 column_default 不为 null 的列。)