PostgreSQL 插入时间与更新时间（qbit）

前言

• PostgreSQL 在数据库层面不能像 MySQL 一样设置自动创建 create_time/update_time，自动更新 update_time
• 下文中 create_at 等价于 create_time，update_at 等价于 update_time
• 需要自己创建触发器实现类似效果
• 本文测试环境

# 服务端
Ubuntu LTS 20.04 
PostgreSQL 15.1

# 客户端
Windows 10
pgAdmin4 6.16

• PostgreSQL 的数据类型：https://www.postgresql.org/do...
• PostgreSQL 的触发器：https://www.postgresql.org/do...

实现步骤

创建表

CREATE TABLE document (
    id int NOT NULL,
    title varchar(1000) NOT NULL,
    keyword varchar(200)[] NOT NULL,
    create_at timestamptz NOT NULL DEFAULT CURRENT_TIMESTAMP,
    update_at timestamptz NOT NULL DEFAULT CURRENT_TIMESTAMP,
    update_at_change timestamptz NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT document_pkey PRIMARY KEY (id)
);

创建函数

/* 两种情况视自己的需求选择 */
/* 主键相同就更新 update_at*/
CREATE OR REPLACE FUNCTION update_at_func() RETURNS TRIGGER AS $update_at_func$
    BEGIN
        NEW.update_at := current_timestamp;
        RETURN NEW;
    END;
$update_at_func$ LANGUAGE plpgsql;

/*字段内容有真实改变时更新 update_at_change */
CREATE OR REPLACE FUNCTION update_at_change_func() RETURNS TRIGGER AS $update_at_change_func$
  BEGIN
      -- 下面3行打印消息便于理解，实际应用时可以去掉
      raise notice 'TG_NAME: %', TG_NAME;
      raise notice 'NEW: %', NEW;
      raise notice 'OLD: %', OLD;
      IF NEW = OLD THEN
          RETURN OLD;
      ELSE
          NEW.update_at_change := current_timestamp;
          RETURN NEW;
      END IF;
  END;
$update_at_change_func$ LANGUAGE plpgsql;

• 后面一种方式参考的这篇问答：https://stackoverflow.com/que...，里面把 ROW 打开又生成，并且用了 IS DISTINCT FROM，看起来有点复杂，不知道是否有特别的原因。

创建触发器

CREATE OR REPLACE TRIGGER update_at_document BEFORE UPDATE ON document
    FOR EACH ROW EXECUTE FUNCTION update_at_func();
    
CREATE OR REPLACE TRIGGER update_at_change_document BEFORE UPDATE ON document
    FOR EACH ROW EXECUTE FUNCTION update_at_change_func();

数据测试

• upsert 插入数据

INSERT INTO "document" (id, title, keyword)
    VALUES (1, 'hello', ARRAY['w', 'o', 'r', 'l', 'd'])
    ON CONFLICT ("id") DO UPDATE SET title = EXCLUDED.title, keyword = EXCLUDED.keyword;

• 再执行一遍是上面 sql 语句，应该可以看到 update_at 发生了变化，update_at_change 没有发生了变化。
• 执行以下 sql 修改字段内容，应该可以看到 update_at 和 update_at_change 都发生了变化。

INSERT INTO "document" (id, title, keyword)
    VALUES (1, 'hello', ARRAY['w', 'o', 'r', 'l', 'd'])
    ON CONFLICT ("id") DO UPDATE SET title = EXCLUDED.title, keyword = EXCLUDED.keyword;

后记

• 可以用 DO 执行匿名代码块来测试 plpgsql 脚本

DO
$body$
BEGIN
    IF 1 = NULL THEN
        raise notice '1 = NULL';
    ELSE 
        raise notice '1 != NULL';
    END IF;
   
    IF 1 IS DISTINCT FROM NULL THEN
        raise notice '1 IS DISTINCT FROM NULL';
    ELSE 
        raise notice '1 IS NOT DISTINCT FROM NULL';
    END IF;
END;
$body$
LANGUAGE 'plpgsql';

• 不熟悉 plpgsql，可以考虑启用 plpython

本文出自 qbit snap