LoginSignup
1
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?

@nuko_yokohama(nuko yokohama)

PostgreSQL 18がやってくる(8) SQL:2011 application time

Last updated at Posted at 2025-05-03

はじめに

にゃーん。
今日はPostgreSQL 18で追加された「application time」について調べてみました。

What's application time?

この機能自体、全然知らなかったけど、SQL規格（SQL:2011で追加された仕様（"Time period definitions"）のことらしい。

非常にざっくり言うと、時刻によって異なる値を管理する機能のようだ。
例えば業務内容は変わらないけど、年度によって部署名が変わる（弊社でもそうだけど、組織や組織名が数年毎に変わるのよね・・・）、というようなケースで使えるようだ。

PostgreSQL文書（開発版）のD.2. Unsupported Featuresの項番T181 "Application-time period tables"に該当するものだと思う。現時点1でUnsupported Featuesとされている理由は不明（まだSQL規格としての実装が不十分なのか、単に文書の修正漏れなのかは不明）。
本体機能として取り込まれたのはPostgreSQL 18からだが、それ以前の版ではtemporal_tablesという拡張機能で似たようなこと（但しSQL:2011準拠の設計ではなさそう）ができるらしい。

概要

Commitfest 2025-03でコミットされた項目である。

タイトル SQL:2011 application time
Topic SQL Commands
Last modified 2025-03-18 09:47:04
Emails SQL:2011 application time

この項目も例によって、MLでの議論が長大なのだけど、ほとんど内容は追えてない・・・。

SQL文の修正

PostgreSQL文書（開発版）のCREATE TABLEに以下の修正が入っている。

PostgreSQL 17とPostgreSQL 18のSQL構文(Synopsis)を比較し、この機能に関するSQL文構文の差分は以下のように見える。

57,58c57,59
<   UNIQUE [ NULLS [ NOT ] DISTINCT ] ( column_name [, ... ] ) index_parameters |
<   PRIMARY KEY ( column_name [, ... ] ) index_parameters |
---
>   NOT NULL column_name [ NO INHERIT ] |
>   UNIQUE [ NULLS [ NOT ] DISTINCT ] ( column_name [, ... ] [, column_name WITHOUT OVERLAPS ] ) index_parameters |
>   PRIMARY KEY ( column_name [, ... ] [, column_name WITHOUT OVERLAPS ] ) index_parameters |
60c61
<   FOREIGN KEY ( column_name [, ... ] ) REFERENCES reftable [ ( refcolumn [, ... ] ) ]
---
>   FOREIGN KEY ( column_name [, ... ] [, PERIOD column_name ] ) REFERENCES reftable [ ( refcolumn [, ... ] [, PERIOD refcolumn ] ) ]

おそらく、

  • UNIQUE制約
  • PRIMARY KEY制約
  • FOREIGN KEY制約

にWITHOUT OVERLAPSキーワード（期間重複しない、という意味かな）付きのカラムが指定可能になった、という修正のようだ。（きちんと調べてないけど、ALTER TABLEも同様の修正が入っているはず）。
そして、PERIOD column_nameに指定する型として範囲型（Range Type）を指定する。

"SQL:2011 application time"では期間（日付やタイムスタンプ）を使うことを前提としているように思えるが、PostgreSQLの今回の実装では、特に日付やタイムスタンプに限らず、任意の範囲型を使用することができる。

(This is sometimes called a temporal key, if the column is a range of dates or timestamps, but PostgreSQL allows ranges over any base type.)

とりあえず使ってみる

あるテーブルの列が時刻によって刻々と変わる、という例を試してみる。

以下のようなテーブルを定義する。

CREATE TABLE foo (
  id int,
  name text,
  valid_period daterange,
  -- PK: id(btree_gist) + valid_period)
  CONSTRAINT foo_pk PRIMARY KEY (id, valid_period WITHOUT OVERLAPS)
);
  • テーブルfooはid, name, valid_periodという列を持つ。
  • valid_periodは日付範囲型を使う。
  • PRIMARY KEYとして、idとvalid_period WITHOUT OVERLAPSを指定する（ここがPostgreSQL 18での修正ポイント）。

はまりポイントと回避方法

上記のDDLはそのまま実行しようとすると

$ psql -p 18001 -U postgres testdb -a -f 0_simple_table.sql
-- application time test
DROP TABLE IF EXISTS foo ;
DROP TABLE
CREATE TABLE foo (
  id int,
  name text,
  valid_period daterange,
  -- PK: id(btree_gist) + valid_period)
  CONSTRAINT foo_pk PRIMARY KEY (id, valid_period WITHOUT OVERLAPS)
);
psql:0_simple_table.sql:9: ERROR:  data type integer has no default operator class for access method "gist"
HINT:  You must specify an operator class for the index or define a default operator class for the data type.

ERROR: data type integer has no default operator class for access method "gist"というエラーになる。
最初、なぜ突然にGiSTインデックスメソッドが？と思ったが、どうやら
「btree_gistを使え、とガンダムPostgreSQL文書が言っている」
ようだ。
PostgreSQL 18文書（開発版）のCREATE TABLEのページにこんな記述がある。

The WITHOUT OVERLAPS column must have a range or multirange type. Empty ranges/multiranges are not permitted. The non-WITHOUT OVERLAPS columns of the constraint can be any type that can be compared for equality in a GiST index. By default, only range types are supported, but you can use other types by adding the btree_gist extension (which is the expected way to use this feature).

  • WITHOUT OVERLAPS以外の列は、GiSTインデックスで等しいかどうかを比較できる任意の型を指定する。
  • でも、通常の基本型(integerやtext等)はGiSTインデックス対応の演算子を持ってない。
  • （基本型を含める場合には）btree_gist拡張を追加して使うことができる。

ということらしい。

btree_gist拡張はcontribモジュールに含まれている。
また、主なPaaS（AWS Aurora, Azure Database for PostgreSQL, AlloyDB, OCI PostgreSQL）でも使用可能になっている。

ということで、btree_gist拡張機能を使用可能2にし、CREATE EXTENSIONコマンドでbtree_gistをデータベースに登録する。
登録後は例えばint型でも、GiST対応の演算子が使えるようになるので、元々のDDLも正常に実行できるようになる。

$ psql -p 18001 -U postgres testdb -a -f 1_simple_table.sql
-- application time test
CREATE EXTENSION btree_gist;
CREATE EXTENSION
DROP TABLE IF EXISTS foo ;
psql:1_simple_table.sql:4: NOTICE:  table "foo" does not exist, skipping
DROP TABLE
CREATE TABLE foo (
  id int,
  name text,
  valid_period daterange,
  -- PK: id(btree_gist) + valid_period)
  CONSTRAINT foo_pk PRIMARY KEY (id, valid_period WITHOUT OVERLAPS)
);
CREATE TABLE

作成したテーブルの定義を\d+メタコマンドで確認してみる。

testdb=# \d+ foo
                                               Table "public.foo"
    Column    |   Type    | Collation | Nullable | Default | Storage  | Compression | Stats target | Description
--------------+-----------+-----------+----------+---------+----------+-------------+--------------+-------------
 id           | integer   |           | not null |         | plain    |             |              |
 name         | text      |           |          |         | extended |             |              |
 valid_period | daterange |           | not null |         | extended |             |              |
Indexes:
    "foo_pk" PRIMARY KEY (id, valid_period WITHOUT OVERLAPS)
Not-null constraints:
    "foo_id_not_null" NOT NULL "id"
    "foo_valid_period_not_null" NOT NULL "valid_period"
Access method: heap

明示的に定義したPRIMARY KEY(foo_pk)以外に、NOT NULL 制約が2つ追加されている。これはPRIMARY KEYが、NOT NULL制約かつUNIQUE制約なので自動的にNOT NULL制約も、PRIMARY KEYを構成する列（id, valid_period）につくことになる。

データ挿入

テーブル定義が無事に終わったので、データを挿入する。

$ psql -p 18001 -U postgres testdb -a -f 1_insert_table.sql
-- application time test
INSERT INTO foo VALUES
(1, 'HQ', '[2020-01-01,9999-12-31]'),
(2, 'Dev1-Team1', '[2020-04-01,2022-03-31]'),
(2, 'Dev1-Team2', '[2022-04-01,2024-03-31]'),
(2, 'Dev2-TeamX', '[2024-04-01,2025-03-31]'),
(3, 'Human Recource', '[2020-01-01,9999-12-31]')
;
INSERT 0 5

今回は5つのレコードを挿入する。id=2のレコードを3つ挿入している。この3レコードのvalid_period列は日付範囲型であるが、これが重ならない（OVERLAPしない）ようなデータにしている。（重なりがあると以下のような制約違反エラーになる）

$ psql -p 18001 -U postgres testdb -a -f 1_error_insert.sql
-- application time test
INSERT INTO foo VALUES
(1, 'HQ', '[2020-01-01,9999-12-31]'),
(2, 'Dev1-Team1', '[2020-04-01,2022-03-31]'),
(2, 'Dev1-Team2', '[2022-03-31,2024-03-31]')
;
psql:1_error_insert.sql:6: ERROR:  conflicting key value violates exclusion constraint "foo_pk"
DETAIL:  Key (id, valid_period)=(2, [2022-03-31,2024-04-01)) conflicts with existing key (id, valid_period)=(2, [2020-04-01,2022-04-01)).

検索

5行のレコードを挿入した後、fooテーブルの内容を見てみる。

testdb=# TABLE foo ;
 id |      name      |       valid_period
----+----------------+--------------------------
  1 | HQ             | [2020-01-01,10000-01-01)
  2 | Dev1-Team1     | [2020-04-01,2022-04-01)
  2 | Dev1-Team2     | [2022-04-01,2024-04-01)
  2 | Dev2-TeamX     | [2024-04-01,2025-04-01)
  3 | Human Recource | [2020-01-01,10000-01-01)
(5 rows)

"application time"っぽい検索をするために、条件として「ある日」時点の部署名（name）を検索してみる。
このときに、valid_period列に条件を指定するが、範囲型なのでフツーの=演算子とかは使えない。

testdb=# SELECT * FROM foo WHERE id = 2 AND valid_period = '2022-01-01';
ERROR:  malformed range literal: "2022-01-01"
LINE 1: SELECT * FROM foo WHERE id = 2 AND valid_period = '2022-01-0...
                                                          ^
DETAIL:  Missing left parenthesis or bracket.

たとえば、'2022-01-01'時点の部署名を検索したい場合には、範囲型 @> 日付のように@>を使う必要がある（正確には @>演算子は包含、つまりを'2022-01-01'を含む範囲を示す）。

testdb=# SELECT * FROM foo WHERE valid_period @> '2022-01-01'::date;
 id |      name      |       valid_period
----+----------------+--------------------------
  1 | HQ             | [2020-01-01,10000-01-01)
  2 | Dev1-Team1     | [2020-04-01,2022-04-01)
  3 | Human Recource | [2020-01-01,10000-01-01)
(3 rows)

testdb=# SELECT * FROM foo WHERE valid_period @> '2023-01-01'::date;
 id |      name      |       valid_period
----+----------------+--------------------------
  1 | HQ             | [2020-01-01,10000-01-01)
  2 | Dev1-Team2     | [2022-04-01,2024-04-01)
  3 | Human Recource | [2020-01-01,10000-01-01)
(3 rows)

testdb=# SELECT * FROM foo WHERE valid_period @> '2025-01-01'::date;
 id |      name      |       valid_period
----+----------------+--------------------------
  1 | HQ             | [2020-01-01,10000-01-01)
  2 | Dev2-TeamX     | [2024-04-01,2025-04-01)
  3 | Human Recource | [2020-01-01,10000-01-01)
(3 rows)

idとvalid_periodの条件によって、application time相当の検索を実現している、ということのようだ。

なお、PostgreSQLのapplication nameの（現状の）実装では、範囲型を使っているので、範囲型の演算子は全て使える。
例えば、「'2022-03-01'から'2022-04-30'までの部署名を検索したい」という検索の場合、範囲型 && 範囲型のような演算子を使うこともできる。

testdb=# SELECT * FROM foo WHERE id = 2 AND valid_period && '[2022-03-01,2022-04-30]';
 id |    name    |      valid_period
----+------------+-------------------------
  2 | Dev1-Team1 | [2020-04-01,2022-04-01)
  2 | Dev1-Team2 | [2022-04-01,2024-04-01)
(2 rows)

おわりに

今回は目玉機能の一つになるかもしれない（個人の感想です）、"SQL:2011 application time"について調べてみました。
PostgreSQL 18での実装は、制約にOVERLAPSの指定を追加可能にしたというものなので、SQL:2011の規格に完全に準拠したものかは、正直自分にはよくわからないとこもありますが、なかなか面白い機能だと思います。

ただ、基本型と組み合わせる場合、btree_gist拡張機能を追加しないといけないのと、範囲型演算子を使わないといけないのが、ちょい面倒な印象・・・。

  1. 2025-05-03現在。 ↩

  2. ソースビルドの場合、contrib/btree_gist ディレクトリ配下でmake, make installする。RPMパッケージなら*contrib*.rpmに入っているはず。PaaS上の場合、それぞれの環境で拡張機能を有効化する設定があるので、それに従う。 ↩

1
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
1
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?