PostgreSQLでテーブル作成のテンプレ

テーブル作成

• <table>: テーブル名
• <c1> ~ <c4>: カラム名

.sql

CREATE TABLE <table>
(
  id SERIAL NOT NULL PRIMARY KEY, -- ID
  <c1> VARCHAR(400) NOT NULL,  -- 短い文字列(400文字まで)
  <c2> TEXT NOT NULL, -- 長い文字列
  <c3> INTEGER NOT NULL, -- 整数
  <c4> NUMERIC NOT NULL, -- 小数
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, -- 作成日時
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP -- 更新日時
);

サンプルデータの挿入

.sql

INSERT INTO <table> 
(
  <c1>, 
  <c2>,
  <c3>,
  <c4>, 
  created_at, 
  updated_at
) 
VALUES

-- 先頭サンプル(必須データ等)
(
  ‘A’,
  ‘BBB’,
  1,
  0.01, 
  TO_TIMESTAMP(‘2000/01/01 00:00:00', 'YYYY/MM/DD HH24:MI:SS'), 
  TO_TIMESTAMP('2010/12/31 23:59:59', 'YYYY/MM/DD HH24:MI:SS')
)

-- 繰り返しサンプル(ランダムに生成したデータ等、ループ部分)
,(
  ‘X’,
  ‘YYY’,
  9,
  0.09, 
  TO_TIMESTAMP('2007/01/09 09:42:00', 'YYYY/MM/DD HH24:MI:SS'),
  TO_TIMESTAMP('2010/01/27 09:41:00', 'YYYY/MM/DD HH24:MI:SS’)
)

-- 末尾のセミコロン
;

作成/更新日時の自動コントロール

• 作成日時は変更できないようにする
• 更新日時は自動で記録する

.sql

-- 関数の作成 (初回のみ)
CREATE FUNCTION auto_timestamp()
RETURNS trigger 
LANGUAGE plpgsql
AS $$
  BEGIN 
    new.created_at = old.created_at;
    new.updated_at = now();
    return new; 
  END; 
$$;

-- テーブルを関数と紐付ける (テーブルの数だけ必要)
CREATE TRIGGER timestamp_<table>
BEFORE UPDATE ON <table>
FOR EACH ROW 
EXECUTE PROCEDURE auto_timestamp();

以上、
自分用のメモを共有させていただきました。
誤りがありましたら指摘をお願いします🙇‍♂️