关于mysql:PostgreSQL-插入时间与更新时间qbit

前言

• PostgreSQL 在数据库层面不能像 MySQL 一样设置主动创立 create_time/update_time，自动更新 update_time
• 下文中 create_at 等价于 create_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
        IF ROW(NEW.*) IS DISTINCT FROM ROW(OLD.*) THEN
              NEW.update_at_change := current_timestamp;
            RETURN NEW;
        ELSE
              RETURN OLD;
        END IF;
    END;
$update_at_change_func$ LANGUAGE plpgsql;

• 前面一种形式参考的这篇问答：https://stackoverflow.com/que…

创立触发器

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;

本文出自 qbit snap