[PostgreSQL] created_at, update_atを自動で入力させる

実現したいこと

テーブルにcreated_at、updated_atのカラムを用意し、

• created_atはデータ挿入時
• updated_atはデータ挿入時とデータ更新時

に自動でそのときの日時が入るように設定し、アプリケーション側ではこの2つのカラムのことをなにも考えなくて良いようにしたい

MySQLであれば以下のように設定するだけだがPostgreSQLでは少し複雑なのでメモとして残す。

MySQL.sql

CREATE TABLE your_table (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

実現方法

1. 初期値を現在日時としてテーブル作成

「created_at」と「update_at」カラムのDEFAULTにCURRENT_TIMESTAMPを設定して「your_table」テーブルを作成している。

PostgreSQL.sql

CREATE TABLE your_table (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

2. データが更新された時に日時を更新する関数を作成する

「update_at」カラムを現在時刻にする「update_timestamp()」という関数を作成している。

PostgreSQL.sql

CREATE OR REPLACE FUNCTION update_timestamp()
RETURNS TRIGGER AS $$
BEGIN
  NEW.updated_at = NOW();
  RETURN NEW;
END;
$$ language 'plpgsql';

3. テーブルのトリガーに作成した関数を設定する

「your_table」テーブルに「update_your_table_modtime」という名前のトリガー（your_tableを更新する前に「update_timestamp()を実行する）を作成している。

PostgreSQL.sql

CREATE TRIGGER update_your_table_modtime
BEFORE UPDATE ON your_table
FOR EACH ROW
EXECUTE PROCEDURE update_timestamp();