2
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

@yuyasat

更新時刻カラムの値更新および履歴テーブルへレコードを挿入する

2
Last updated at Posted at 2025-09-24

はじめに

最近のテーブル設計においては、レコード初期挿入時刻(created_at)と更新時刻(updated_at)のカラムを追加するように設計することが多いです。これはRailsをはじめとしたWebフレームワークで採用されている設計です。ActiveRecord、EFCoreなどのといったORマッパーを利用していれば、updated_atの値を直接的に意識することは少ないと思いますが、処理によってはSQLを直接実行したい場合があります。また、値の更新だけではなく、更新があったら履歴テーブルに格納しておきたいということもあります。これらをSQLで実装するにはどうすればいいのかということを調べたので、自分の備忘録も兼ねて記載します。

1. 今回説明で利用するテーブル生成のためのDDLと初期データ

店舗テーブル(stores)とその変更履歴を保持する店舗履歴テーブル(store_histories)を例に説明します。

CREATE TABLE stores (
  id BIGSERIAL PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  prefecture VARCHAR(255) NOT NULL,
  created_at TIMESTAMP WITHOUT TIME ZONE NOT NULL,
  updated_at TIMESTAMP WITHOUT TIME ZONE NOT NULL
);

CREATE TABLE store_histories (
  id BIGSERIAL PRIMARY KEY,
  store_id BIGINT NOT NULL,
  name VARCHAR(255) NOT NULL,
  prefecture VARCHAR(255) NOT NULL,
  created_at TIMESTAMP WITHOUT TIME ZONE NOT NULL,
  updated_at TIMESTAMP WITHOUT TIME ZONE NOT NULL,
  CONSTRAINT fk_store
    FOREIGN KEY(store_id)
    REFERENCES stores(id)
);

最初に一レコードを追加しておきます。SQLは以下になります。

INSERT INTO stores (
  name, prefecture, created_at, updated_at
)
VALUES (
 'さんぷる店舗名', '東京都', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP
)
;

2. レコード更新時に変更があればupdated_atを更新するSQL

このレコードを更新します。今回は、店舗名をさんぷる店舗名からさんぷる店舗名更新後に変更するとします。この場合、created_atは最初にレコードが作成された時刻を格納したいので、created_atの値はそのままで、updated_atは更新したいということになります。この場合のSQLは以下のようになります。いわゆるmerge文です。

INSERT INTO stores (
  id
  , name
  , prefecture
  , created_at
  , updated_at
)
VALUES (
 1
 , 'さんぷる店舗名更新後'
 , '東京都'
 , CURRENT_TIMESTAMP
 , CURRENT_TIMESTAMP
)
ON CONFLICT ("id")
DO UPDATE SET
  updated_at=(
    CASE
    WHEN (
      stores.name IS NOT DISTINCT FROM EXCLUDED.name
      AND stores.prefecture IS NOT DISTINCT FROM EXCLUDED.prefecture
    ) THEN stores.updated_at
    ELSE CURRENT_TIMESTAMP
    END
  )
  , name=EXCLUDED.name
  , prefecture=EXCLUDED.prefecture
RETURNING id
;

試しに、もう一度このSQLを実行したとしても、nameの値は変更されていないので、updated_atの値は更新されません。

ここで、stores.name IS NOT DISTINCT FROM EXCLUDED.nameついて簡単に説明します。これは、stores.name と EXCLUDED.name が等しい、または両方ともNULLである場合にTRUEとなります。IS NOT DISTINCT FROMについては、NULLとNULLの比較もTRUEにしたい場合に利用する演算子です。1

EXCLUDEDは、INSERT ... ON CONFLICTで使われる特殊なテーブルエイリアスになり、DO UPDATE SET以降で参照できる、新しく挿入しようとした値が格納されるテーブルです。この場合、EXCLUDED.nameには、さんぷる店舗名更新後が格納されています。

CASE文において、変更前のname=さんぷる店舗名とprefecture=東京都の値と、変更後のname=さんぷる店舗名更新後とprefecture=東京都の値を比較し、これらが全て一致している場合は、updated_atはもとのレコードの値を使い、全て一致しなかった場合（どれかが更新された場合）は、updated_atはCURRENT_TIMESTAMPを使うという処理になります。

3. レコード更新後、変更があれば履歴テーブルにレコードを挿入する

さらにこの勢いで、一つのSQL文でstore_historiesテーブルにまでデータを挿入するようにします。SQLは以下になります。

WITH upserted AS (
  INSERT INTO stores (
    id
    , name
    , prefecture
    , created_at
    , updated_at
  )
  VALUES (
   1
   , 'さんぷる店舗名更新後'
   , '神奈川県'
   , CURRENT_TIMESTAMP
   , CURRENT_TIMESTAMP
  )
  ON CONFLICT ("id")
  DO UPDATE SET
    updated_at=(
      CASE
      WHEN (
        stores.name IS NOT DISTINCT FROM EXCLUDED.name
        AND stores.prefecture IS NOT DISTINCT FROM EXCLUDED.prefecture
      ) THEN stores.updated_at
      ELSE CURRENT_TIMESTAMP
      END
    )
    , name=EXCLUDED.name
    , prefecture=EXCLUDED.prefecture
  RETURNING
    id
    , name
    , prefecture
    , created_at
    , updated_at
)
INSERT INTO store_histories(
  store_id
  , name
  , prefecture
  , created_at
  , updated_at
)
SELECT
  *
FROM
  upserted
WHERE
  updated_at = CURRENT_TIMESTAMP
;

RETURNINGを用いて、戻り値を共通テーブルに一時的に格納し、その値を履歴テーブル(store_histories)に挿入(INSERT INTO)します。この時、updated_atがCURRENT_TIMESTAMPの場合のみ挿入します。CURRENT_TIMESTAMPはPostgreSQLの場合、トランザクションの開始日時で、一つのクエリの中で同じ値になります。データの変更がなかった場合は、昔の時刻、変更があった場合は、storesへのINSERT INTOのときと同じ時刻になっているので、この値で絞り込みを行い、変更があった場合のみに履歴テーブルに挿入するようにします。

補足：ON CONFLCITに設定するカラムについて

ここで、ON CONFLCITに設定するカラムについて説明を補足します。ここで指定しているカラムは、UNIQUE CONSTRAINTが指定されている要があります。

説明のために、先ほどのテーブルにcompany_codeカラムとstore_codeカラムを追加してみます。DDLは以下です。

CREATE TABLE stores (
  id BIGSERIAL PRIMARY KEY,
  company_code VARCHAR(255) NOT NULL,
  store_code VARCHAR(255) NOT NULL,
  name VARCHAR(255) NOT NULL,
  prefecture VARCHAR(255) NOT NULL,
  created_at TIMESTAMP WITHOUT TIME ZONE NOT NULL,
  updated_at TIMESTAMP WITHOUT TIME ZONE NOT NULL
)
;

CREATE TABLE store_histories (
  id BIGSERIAL PRIMARY KEY,
  store_id BIGINT NOT NULL,
  company_code VARCHAR(255) NOT NULL,
  store_code VARCHAR(255) NOT NULL,
  name VARCHAR(255) NOT NULL,
  prefecture VARCHAR(255) NOT NULL,
  created_at TIMESTAMP WITHOUT TIME ZONE NOT NULL,
  updated_at TIMESTAMP WITHOUT TIME ZONE NOT NULL,
  CONSTRAINT fk_store
    FOREIGN KEY(store_id)
    REFERENCES stores(id)
)
;

company_codeとstore_codeに値を追加した初期のデータ挿入のSQLは、以下になります。

INSERT INTO stores (
  company_code, store_code, name, prefecture, created_at, updated_at
)
VALUES (
  'company-code-1', 'store-code-1', 'さんぷる店舗名', '東京都', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP
)
;

ここで、2. レコード更新時に変更があればupdated_atを更新するSQLのように、ON CONFLICT句にcompany_codeとstore_codeを指定してINSERT INTOを実行してみます。

INSERT INTO stores (
  company_code
  , store_code
  , name
  , prefecture
  , created_at
  , updated_at
)
VALUES (
  'company-code-1'
  , 'store-code-1'
  , 'さんぷる店舗名更新後'
  , '神奈川県'
  , CURRENT_TIMESTAMP
  , CURRENT_TIMESTAMP
)
ON CONFLICT ("company_code", "store_code")
DO UPDATE SET
  updated_at=(
    CASE
    WHEN (
      stores.company_code IS NOT DISTINCT FROM EXCLUDED.company_code
      AND stores.store_code IS NOT DISTINCT FROM EXCLUDED.store_code
      AND stores.name IS NOT DISTINCT FROM EXCLUDED.name
      AND stores.prefecture IS NOT DISTINCT FROM EXCLUDED.prefecture
    ) THEN stores.updated_at
    ELSE CURRENT_TIMESTAMP
    END
  )
  , name=EXCLUDED.name
  , prefecture=EXCLUDED.prefecture
RETURNING
  id
  , company_code
  , store_code
  , name
  , prefecture
  , created_at
  , updated_at
;

これを実行すると以下のようなエラーが発生すると思います。

ERROR:  there is no unique or exclusion constraint matching the ON CONFLICT specification

これは、ON CONFLICTに指定しているカラムにユニーク制約や排他制約が指定されていない場合に表示されます。先ほどはidカラムを指定しており、idカラムはPRIMARY KEY指定されているカラムであり、PRIMARY KEYは必然的にユニーク制約となるので、エラーにならなかったのですが、company_codeとstore_codeは現時点では、ユニーク制約をはっていないので、エラーになったということです。
そこで、以下のようにユニーク制約を貼はるようにします。

ALTER TABLE stores ADD UNIQUE(company_code, store_code)
;

ユニーク制約をはり、先ほどのINSERT INTOを実施すればエラーなく実施できると思います。
ここで、間違えないようにしないといけないのは、ユニークインデックスではないということです。

CREATE UNIQUE INDEX stores_company_code_store_code_idx ON stores(company_code, store_code)
;

以下に、ユニーク制約を貼った場合とユニークインデックスを貼った場合のスキーマをそれぞれ載せます。

  • ALTER TABLE ADD UNIQUEだけを実行した時のstoresテーブルのスキーマ詳細
# \d stores;
                                         Table "public.stores"
    Column    |            Type             | Collation | Nullable |              Default
--------------+-----------------------------+-----------+----------+------------------------------------
 id           | bigint                      |           | not null | nextval('stores_id_seq'::regclass)
 company_code | character varying(255)      |           | not null |
 store_code   | character varying(255)      |           | not null |
 name         | character varying(255)      |           | not null |
 prefecture   | character varying(255)      |           | not null |
 created_at   | timestamp without time zone |           | not null |
 updated_at   | timestamp without time zone |           | not null |
Indexes:
    "stores_pkey" PRIMARY KEY, btree (id)
    "stores_company_code_store_code_key" UNIQUE CONSTRAINT, btree (company_code, store_code)
Referenced by:
    TABLE "store_histories" CONSTRAINT "fk_store" FOREIGN KEY (store_id) REFERENCES stores(id)
  • CREATE UNIQUE INDEXだけを実行した時のstoresテーブルのスキーマ詳細
# \d stores;
                                         Table "public.stores"
    Column    |            Type             | Collation | Nullable |              Default
--------------+-----------------------------+-----------+----------+------------------------------------
 id           | bigint                      |           | not null | nextval('stores_id_seq'::regclass)
 company_code | character varying(255)      |           | not null |
 store_code   | character varying(255)      |           | not null |
 name         | character varying(255)      |           | not null |
 prefecture   | character varying(255)      |           | not null |
 created_at   | timestamp without time zone |           | not null |
 updated_at   | timestamp without time zone |           | not null |
Indexes:
    "stores_pkey" PRIMARY KEY, btree (id)
    "stores_company_code_store_code_idx" UNIQUE, btree (company_code, store_code)
Referenced by:
    TABLE "store_histories" CONSTRAINT "fk_store" FOREIGN KEY (store_id) REFERENCES stores(id)

違いとしては以下のようになりCONSTRAINTがつくかつかないかの違いなのですがこの違いがINSERT INTO ... ON CONFLICTが使える稼働かの違いになってくるわけです。

-"stores_company_code_store_code_idx" UNIQUE, btree (company_code, store_code)
+"stores_company_code_store_code_key" UNIQUE CONSTRAINT, btree (company_code, store_code)

  1. https://qiita.com/gooddoog/items/ad8c20b1734d5811bf78 ↩

2
0
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
2
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?