2015/04/04

PostgreSQL 一定期間ごとに一行だけデータを持てるテーブル

[PostgreSQL]
このブログが「一日一記事」にしているのと近い話です。一日とか一時間とか単位期間を決めて、各期間ごとに一行しかデータを持てない(データがなくてもよい)単純なテーブルを作ってみます。

CREATE TABLE "201504"."04_test" (
ts timestamp PRIMARY KEY DEFAULT now(), content text NOT NULL
) ;
CREATE UNIQUE INDEX ON "201504"."04_test" (
date_trunc('hour', ts) -- // ここで期間の幅を決める
) ;

上のようにテーブル名を二回書くのが面倒なら、PostgreSQL 9.0 で追加された排他制約(詳細 : 日本語ドキュメント)を使って次のようにも書けます。

CREATE TABLE "201504"."04_test" (
ts timestamp PRIMARY KEY DEFAULT now(), content text NOT NULL
, EXCLUDE (date_trunc('hour', ts) WITH =)
) ;

↓ テーブルを作りデータを入力した後の様子。以下、実行環境は Windows 7 32bit + PostgreSQL Portable 9.4 + pgAdmin Portable 1.20 です。


期間内(ここでは一時間)に既存データがなければ、上のとおり INSERT 文がそのまま通ります。続いて、同期間のうちに新規データを追加しようとすると ↓ 弾かれます。ERROR: conflicting key value violates exclusion constraint ... は排他制約違反の意味。冒頭のクエリのように一意インデクス制約だと、エラーメッセージが少し違うかもしれません。


↓ 期間が変われば、一回だけデータの新規追加ができるようになります。同期間内に二件以上の INSERT ができないのは、上と同じ。


列 ts のデフォルトに now() を設定しているので、データ各行の INSERT 時にタイムスタンプが自動的に入力されます。同じ期間(ここでは一時間)で行が既存なら制約違反になり弾く仕組み。実質、「年月日 + 時」など期間に丸めた別の列を作って一意制約を付けるのと同じです。

これで「一定期間ごとに一行だけデータを持てるテーブル」ができたので、次に UPDATE への制約を

・ 期間内であれば可
・ 期間を過ぎたら不可

と単純に仮定し、データ入力・修正を常に ↓ のストアド関数から行うルールにします。つまり INSERT や UPDATE のクエリでタイムスタンプが手動変更される余地をなくし、作業者がデータ入力・修正に専念するイメージ。(トリガでの本格的な制約は、いま勉強中なのでいずれ…)

CREATE OR REPLACE FUNCTION "201504"."04_simple_upsert"(text)
RETURNS void LANGUAGE plpgsql AS $BODY$
BEGIN
BEGIN
INSERT INTO "201504"."04_test" (content) VALUES ($1) ;
EXCEPTION WHEN OTHERS THEN
UPDATE "201504"."04_test"
SET ts = now(), content = $1
WHERE ts = (
SELECT ts FROM "201504"."04_test" ORDER BY 1 DESC LIMIT 1
) ;
END ;
END ;
$BODY$ ;

既存の行があれば UPDATE、なければ INSERT という UPSERT の原始的な感じです。まず INSERT を試み、テーブルに設定した制約で弾かれたら(例外を捕捉)最新の期間にデータが既存なので、その行を WHERE で探し UPDATE。その際、タイムスタンプも現在時刻に更新します。

対象テーブルを三箇所もべた書きしているのは今いちですが、ともかくデータ操作の窓口をこのストアドに集約すると、次のような状況になります。

・ある期間で一回データを入力したら、期間中に可能なのは修正だけ。
・期間が変わると自動的にデータが確定し、変更できない(データ無しなら、その事実が確定)。
・タイムスタンプ列に、入力または修正した最終時刻が自動的に記録される。

もう少し操作側でできることを加えて(例えばデータ破棄、入力済データの表示など)作業日記なんかに使えないかなと検討中。また期間の幅について、date_trunc 関数で指定できる「日」「時」などは簡単なのに対し、半日とか三時間とかだと少し面倒そうです。
×

この広告は1年以上新しい記事の投稿がないブログに表示されております。