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

在数据库实战开发的过程中，我们经常会遇到一些这样那样的问题，然后要卡好半天，等问题解决了才发现原来一些细节知识点还是没有掌握好。今天golang学习网就整理分享《PostgreSQL 插入时间与更新时间（qbit）》，聊聊MySQL、postgresql、olap，希望可以帮助到正在努力赚钱的你。

前言

• 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

今天关于《PostgreSQL 插入时间与更新时间（qbit）》的内容介绍就到此结束，如果有什么疑问或者建议，可以在golang学习网公众号下多多回复交流；文中若有不正之处，也希望回复留言以告知！