PostgreSQL 插入時間與更新時間（qbit）

qbit發表於2022-12-01
SQL

前言

  • PostgreSQL 在資料庫層面不能像 MySQL 一樣設定自動建立 create_time/update_time，自動更新 update_time
  • 下文中 create_at 等價於 create_time
  • 需要自己建立觸發器實現類似效果
  • 本文測試環境
# 服務端
Ubuntu LTS 20.04 
PostgreSQL 15.1

# 客戶端
Windows 10
pgAdmin4 6.16

實現步驟

建立表

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;

建立觸發器

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

相關文章