2022年4月18日

あまり知られていないPostgreSQLの機能

PostgreSQL

(2021-11-08)by Haki Benita

本記事は、原著者の許諾のもとに翻訳・掲載しております。

あなたが知らない既存機能があるかもしれません！

マイクロソフト社は2006年、Microsoft Officeの新バージョンで追加してほしい機能について、顧客調査を実施しました。驚いたことに、ユーザが希望した機能の90%以上はすでに実装されており、その存在が知られていないだけであることが判明しました。機能の「見つけにくさ」の問題の解決策として同社が考案したのが、現在のMicrosoft Office製品でおなじみの「リボンUI」です。

この問題はOfficeに限ったものではありません。日々使用するツールの機能をすべて把握している人はほとんどいません。PostgreSQLのように大規模なツールであればなおさらです。数週間前にPostgreSQL 14がリリースされたばかりなので、この機会にPostgreSQLのあまり知られていない機能に注目してみたいと思います。

この記事では、PostgreSQLのあまり知られていない機能を紹介します。

Illustration by Eleanor Wright

UPSERTにおいて更新、挿入された行数の取得
特定の列の権限付与
複数パターンに対するマッチング
シーケンスを進めることなく現在値を確認する
複数行のSQLで\COPYを使用する
自動生成キーの値の設定を防ぐ
ピボットテーブルを作成するもう2つの方法
ドル引用
データベースオブジェクトに関するコメント
データベースごとに個別の履歴ファイルを維持する
大文字の予約語の自動補完
スリープ時間の指定
サブクエリを使用せず、グループの最初または最後の行を取得する
拡張機能なしでUUIDを生成する
再現可能なランダムデータの生成
直ちに検証することなく制約を追加する
PostgreSQLにおけるシノニム
重複レンジを探す

UPSERTにおいて更新、挿入された行数の取得

INSERT ON CONFLICTは、Extract(抽出) Transform(変換) Load(読み込み) プロセスでは特に便利なコマンドであり、Oracleの「MERGE」に相当し、「UPSERT」（UPDATEとINSERTを組み合わせた造語）とも呼ばれます。INSERT文のON CONFLICT句を使用することで、キー列に重複がある場合の処理をデータベースに指示できます。

以下は従業員テーブルのデータを同期するためのクエリの例です。

db=# WITH new_employees AS (
    SELECT * FROM (VALUES
        ('George', 'Sales',    'Manager',   1000),
        ('Jane',   'R&D',      'Developer', 1200)
    ) AS t(
         name,      department, role,       salary
    )
)
INSERT INTO employees (name, department, role, salary)
SELECT name, department, role, salary
FROM new_employees
ON CONFLICT (name) DO UPDATE SET
    department = EXCLUDED.department,
    role = EXCLUDED.role,
    salary = EXCLUDED.salary
RETURNING *;

  name  │ department │   role    │ salary
────────┼────────────┼───────────┼────────
 George │ Sales      │ Manager   │   1000
 Jane   │ R&D        │ Developer │   1200
INSERT 0 2

このクエリは、新しい従業員データをテーブルに挿入します。追加しようとした従業員の名前がすでに存在する場合、データを挿入する代わりにその行が更新されます。

RETURNING *

WITHとRETURNINGを使用して完全なプロセスを実行する方法はこちらをご覧ください。

上記コマンドの出力INSERT 0 2から、影響のある従業員が2名いることが分かります。では、挿入された行数と更新された行数はいくつでしょうか。この出力からは判断できません。

筆者が同様のクエリを使用したETLプロセスのロギングを改善する方法を探していた際、Stack Overflowで投稿された質問に対する回答に、これと全く同じ問題に対する有効な解決策を偶然見つけました。

db=# WITH new_employees AS (
    SELECT * FROM (VALUES
        ('George', 'Sales',    'Manager',   1000),
        ('Jane',   'R&D',      'Developer', 1200)
    ) AS t(
         name,      department, role,       salary
    )
)
INSERT INTO employees (name, department, role, salary)
SELECT name, department, role, salary
FROM new_employees
ON CONFLICT (name) DO UPDATE SET
    department = EXCLUDED.department,
    role = EXCLUDED.role,
    salary = EXCLUDED.salary
RETURNING *, (xmax = 0) AS inserted;

  name  │ department │   role    │ salary │ inserted
────────┼────────────┼───────────┼────────┼──────────
 Jane   │ R&D        │ Developer │   1200 │ t
 George │ Sales      │ Manager   │   1000 │ f
INSERT 0 2

RETURNING句の違いに注目してください。こちらの例では、xmaxという特殊な列を使用して挿入された行数を計算するinsertedという計算済みフィールドが含まれています。コマンドによって返されたデータから、「Jane」の行が新たに挿入されたものの、「George」はすでにテーブルに存在したため、その行は更新されたことが分かります。

xmax列は、特殊なシステム列です。

削除トランザクションの識別情報（トランザクションID）です。削除されていない行ではゼロです。 PostgreSQLでは、行が更新されると古いバージョンが削除され、xmaxに削除トランザクションのIDが保持されます。行が挿入されると、古い行の削除は行われないため、xmaxの値は0になります。この解決策では、この挙動を賢く利用して更新された行と挿入された行を区別しています。

特定の列の権限付与

認証情報、パスワード、個人を特定できる情報などのセンシティブ情報を含むユーザテーブルがあるとします。

db=# CREATE TABLE users (
    id INT,
    username VARCHAR(20),
    personal_id VARCHAR(10),
    password_hash VARCHAR(256)
);
CREATE TABLE

db=# INSERT INTO users VALUES (1, 'haki', '12222227', 'super-secret-hash');
INSERT 1 0

このテーブルは、アナリストなど組織内のさまざまな人が、データにアクセスして随時レポートを作成するために使用します。アナリストにアクセスを許可するため、データベースに特別ユーザを追加します。

db=# CREATE USER analyst;
CREATE USER

db=# GRANT SELECT ON users TO analyst;
GRANT

現在、データベース「db」にユーザ「analyst」として接続されています。

db=# \connect db analyst
You are now connected to database "db" as user "analyst".

db=> SELECT * FROM users;
 id │ username │ personal_id │   password_hash
────┼──────────┼─────────────┼───────────────────
  1 │ haki     │ 12222227    │ super-secret-hash

前に述べたように、アナリストはレポートを作成し、分析を行うためにユーザデータにアクセスしますが、センシティブ情報や個人を特定できる情報にアクセスできることは望ましくありません。

ユーザがテーブル内でアクセスできるデータを詳細に制御するため、PostgreSQLでは特定の列のみを対象に権限を付与できます。

db=# \connect db postgres
You are now connected to database "db" as user "postgres".

db=# REVOKE SELECT ON users FROM analyst;
REVOKE

db=# GRANT SELECT (id, username) ON users TO analyst;
GRANT

テーブル上の既存の選択権限を取り消した後、id列とusername列のみを対象として、analystに選択権限を付与しました。これで、analystはこれら以外の列にはアクセスできなくなります。

db=# \connect db analyst
You are now connected to database "db" as user "analyst".

db=> SELECT * FROM users;
ERROR:  permission denied for table users

db=> SELECT id, username, personal_id FROM users;
ERROR:  permission denied for table users

db=> SELECT id, username FROM users;
 id │ username
────┼──────────
  1 │ haki

ユーザ「analyst」が制限された列に明示的、あるいは「*」を使用して黙示的にアクセスしようとすると、「permission denied」エラーが返されます。

複数パターンに対するマッチング

SQLでは、パターンマッチングをよく使用します。以下は「gmail.com」のメールアカウントを保有するユーザを検索するクエリの例です。

SELECT *
FROM users
WHERE email LIKE '%@gmail.com';

このクエリは、ワイルドカード「%」を使用し、メールアドレスが「@gmail.com」で終わるユーザを検索します。では、同じクエリの中で「yahoo.com」のメールアカウントを保有するユーザも検索したい場合はどうすればよいでしょうか。

SELECT *
FROM users
WHERE
    email LIKE '%@gmail.com'
    OR email LIKE '%@yahoo.com'

いずれかのパターンに対するマッチングを行うには、OR条件を指定する方法があります。しかし、PostgreSQLでは、複数パターンに対してマッチングを行う方法は他にもあります。

SELECT *
FROM users
WHERE email SIMILAR TO '%@gmail.com|%@yahoo.com'

SIMILAR TO演算子を使用することで、単純なクエリにより複数パターンとのマッチングを行うことができます。

regexpを使用して複数パターンとのマッチングを行う方法もあります。

SELECT *
FROM users
WHERE email ~ '@gmail\.com$|@yahoo\.com$'

regexpを使用する際には注意が必要です。ピリオド（.）は任意の文字にマッチするため、gmail.comやyahoo.comに含まれるピリオドとマッチングするには、「.」のようにエスケープ文字を追加する必要があります。

筆者がこの情報をTwitterに投稿したところ、いくつかの興味深い反応が得られました。Pythonに対応したPostgreSQLドライバであるpsycopgの公式アカウントから届いたコメントは、別の方法を提案しました。

SELECT *
FROM users
WHERE email ~ ANY(ARRAY['@gmail\.com$', '@yahoo\.com$'])

このクエリでは、ANY演算子を使用してさまざまなパターンに対してマッチングを行います。メールアドレスがいずれかのパターンにマッチすれば、条件は真となります。このアプローチは、Pythonなどのホスト言語から行うとやりやすいでしょう。

with connection.cursor() as cursor:
    cursor.execute('''
        SELECT *
        FROM users
        WHERE email ~ ANY(ARRAY%(patterns)s)
    ''' % {
        'patterns': [
            '@gmail\.com$',
            '@yahoo\.com$',
        ],
    })

SIMILAR TO演算子を用いた前述のアプローチとは異なり、ANY演算子を使用すればパターンのリストを変数に結合できます。

シーケンスを進めることなく現在値を確認する

シーケンスの現在値を確認する必要がある場合、おそらく最初にcurrvalを使用するでしょう。

db=# SELECT currval('sale_id_seq');
ERROR:  currval of sequence "sale_id_seq" is not yet defined in this session

その場合、筆者と同じように、currvalはシーケンスが現在のセッションで定義または使用されていなければ使えないことに気付くと思います。特に理由もなくシーケンスを進めることは通常避けたいため、これは好ましい解決策ではありません。

PostgreSQL 10では、シーケンスに関する情報に容易にアクセスできるよう、pg_sequencesビューが追加されました。

db=# SELECT * FROM pg_sequences WHERE sequencename = 'sale_id_seq';
─[ RECORD 1 ]─┬────────────
schemaname    │ public
sequencename  │ sale_id_seq
sequenceowner │ db
data_type     │ integer
start_value   │ 1
min_value     │ 1
max_value     │ 2147483647
increment_by  │ 1
cycle         │ f
cache_size    │ 1
last_value    │ 155

このテーブルでも現在値は確認できますが、これは「あまり知られていない機能」というよりも、情報スキーマにおけるテーブルの1つに過ぎません。

ドキュメントには記載されていないのですが、pgsequencelast_valueという関数を使用してシーケンスの現在値を確認する方法もあります。

db=# SELECT pg_sequence_last_value('sale_id_seq');
 pg_sequence_last_value
────────────────────────
                   155

なぜこの関数がドキュメントに記載されていないのかは定かではありませんが、筆者が確認したところ、公式ドキュメントの中では言及されていませんでした。使用する場合はその点も考慮してください。

興味深いことに、このテーマについて調査をしているとき、テーブルと同じようにシーケンスもクエリできることを発見しました。

db=# SELECT * FROM sale_id_seq;

 last_value │ log_cnt │ is_called
────────────┼─────────┼───────────
        155 │      10 │ t

PostgreSQLでは他にどのような種類のオブジェクトをクエリでき、どのような値が返されるのか気になるところです。

注意すべき点として、この機能はシーケンスを大まかに確認するためだけに使用してください。この出力の値をもとにIDの更新を試みるべきではありません。IDを更新したい場合はnextvalを使用してください。

複数行のSQLで\COPYを使用する

psqlを頻繁に使用する人は、データベースからデータをエクスポートするために\COPYをかなりの頻度で使用しているのではないでしょうか。筆者は頻繁に使用しています。\COPYに関する不満点の1つは、複数行のクエリに対応していないことです。

db=# \COPY (
\copy: parse error at end of line

\COPYコマンドに新しい行を追加しようとすると、このようなエラーメッセージが表示されます。この制約を回避するため、筆者はまずビューを使用しました。

db=# CREATE VIEW v_department_dbas AS
    SELECT department, count(*) AS employees
    FROM emp
    WHERE role = 'dba'
    GROUP BY department
    ORDER BY employees;
CREATE VIEW

db=# \COPY (SELECT * FROM v_department_dbas) TO department_dbas.csv WITH CSV HEADER;
COPY 5

db=# DROP VIEW v_department_dbas;
DROP VIEW;

この方法も有効ではあるものの、途中で何かがうまく行かなかった場合、ビューが散乱する結果になりかねません。スキーマはきれいに整理された状態にしておきたいので、作業後のクリーンアップを自動的に行える方法を探してみました。検索すると、一時ビューがすぐに出てきました。

db=# CREATE TEMPORARY VIEW v_department_dbas AS # ...
CREATE VIEW

db=# \COPY (SELECT * FROM v_department_dbas) TO department_dbas.csv WITH CSV HEADER;
COPY 5

一時ビューは、セッションが終了すると自動的に削除されるため、これを使用することで作業後のクリーンアップを行う必要がなくなりました。しばらく一時ビューを使用していましたが、psqlドキュメントの中に素晴らしい発見がありました。

db=# COPY (
    SELECT department, count(*) AS employees
    FROM emp
    WHERE role = 'dba'
    GROUP BY department
    ORDER BY employees
) TO STDOUT WITH CSV HEADER \g department_dbas.csv
COPY 5

実に秀逸ではないでしょうか。細かく見ていきましょう。

\COPYの代わりにCOPYを使用する：COPYコマンドはサーバ上で実行されるサーバコマンドであり、\COPYは同じインターフェースを持つpsqlコマンドです。したがって、\COPYは複数行のクエリに対応していませんが、COPYは対応しています。
STDOUTに結果を書き込む：クエリの結果は、COPYを使用してサーバ上のディレクトリに書き込むか、TO STDOUTを使用して標準出力に書き込むことができます。
\gを使用してSTDOUTをローカルファイルに書き込む：最後に、psqlは出力を標準出力からファイルに書き込むためのコマンドを提供しています。

これら3つの機能を組み合わせることで、求めていた通りの結果が得られました。

コピーの達人

大量のデータを動かす場合、Fastest Way to Load Data Into PostgreSQL Using Pythonもチェックしてみてください。

自動生成キーの値の設定を防ぐ

PostgreSQLで自動生成の主キーを使用している場合、おそらくまだSERIALデータ型を使用しているのではないでしょうか。

CREATE TABLE sale (
    id SERIAL PRIMARY KEY,
    sold_at TIMESTAMPTZ,
    amount INT
);

裏では、PostgreSQLは行が追加された場合に使用するシーケンスを作成しています。

db=# INSERT INTO sale (sold_at, amount) VALUES (now(), 1000);
INSERT 0 1

db=# SELECT * FROM sale;
 id │           sold_at             │ amount
────┼───────────────────────────────┼────────
  1 │ 2021-09-25 10:06:56.646298+03 │   1000

SERIALデータ型はPostgreSQLに固有のものであり、いくつか既知の問題があります。したがって、バージョン10以降、SERIALデータ型はやんわり非推奨とされ、id列の使用が推奨されています。

CREATE TABLE sale (
    id INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    sold_at TIMESTAMPTZ,
    amount INT
);

id列は、SERIALデータ型の仕組みと非常に似ています。

db=# INSERT INTO sale (sold_at, amount) VALUES (now(), 1000);
INSERT 0 1

db=# SELECT * FROM sale;
 id │           sold_at             │ amount
────┼───────────────────────────────┼────────
  1 │ 2021-09-25 10:11:57.771121+03 │   1000

では、次のシナリオを検討してみてください。

db=# INSERT INTO sale (id, sold_at, amount) VALUES (2, now(), 1000);
INSERT 0 1

db=# INSERT INTO sale (sold_at, amount) VALUES (now(), 1000);
ERROR:  duplicate key value violates unique constraint "sale_pkey"
DETAIL:  Key (id)=(2) already exists.

なぜエラーになったのでしょうか？

最初のINSERTコマンドは、id列の値2を明示的に指定しているため、シーケンスは使用されませんでした。
2つ目のINSERTコマンドはidの値を指定していないため、シーケンスが使用されます。このコマンドは、シーケンスの次の値が偶然2であったため、一意性制約違反によりエラーになりました。

自動採番するIDを手動で設定する必要があることはまれであり、手動で設定すると混乱を招くおそれがあります。では、ユーザが設定を行わないようにするにはどうすればよいでしょうか。

CREATE TABLE sale (
    id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    sold_at TIMESTAMPTZ,
    amount INT
);

GENERATED BY DEFAULTの代わりにGENERATED ALWAYSを使用するのです。両者の違いを理解するために、再び同じシナリオを試してみましょう。

db=# INSERT INTO sale (sold_at, amount) VALUES (now(), 1000);
INSERT 0 1

db=# INSERT INTO sale (id, sold_at, amount) VALUES (2, now(), 1000);
ERROR:  cannot insert into column "id"
DETAIL:  Column "id" is an identity column defined as GENERATED ALWAYS.
HINT:  Use OVERRIDING SYSTEM VALUE to override.

何が変わりましたか？

最初のINSERTコマンドはidの値を指定せず、正常に完了します。しかし、2つ目のINSERTコマンドはidに値2を設定しようとしてエラーになっています。
親切にも、PostgreSQLではid列に明示的に値を設定したい場合の解決策がエラーメッセージに記述されています。

db=# INSERT INTO sale (id, sold_at, amount)
OVERRIDING SYSTEM VALUE VALUES (2, now(), 1000);

INSERT 0 1

INSERTコマンドにOVERRIDING SYSTEM VALUEを追加することで、id列の値を設定可能にするようPostgreSQLに明示的に指示できます。それでも、一意性制約違反に対処する必要はあるかもしれませんが、それをPostgreSQLのせいにすることはもはやできません。

ピボットテーブルを作成するもう2つの方法

以前執筆した記事の中で、筆者は条件付き集計を使用してピボットテーブルを作成する方法を説明しました。その記事を書いた後、PostgreSQLでピボットテーブルを生成する方法をさらに2つ発見しました。

各部門の職務ごとの従業員数を調べたいと思います。

db=# WITH employees AS (
    SELECT * FROM (VALUES
        ('Haki',    'R&D',      'Manager'),
        ('Dan',     'R&D',      'Developer'),
        ('Jax',     'R&D',      'Developer'),
        ('George',  'Sales',    'Manager'),
        ('Bill',    'Sales',    'Developer'),
        ('David',   'Sales',    'Developer')
    ) AS t(
        name,       department,  role
    )
)
SELECT role, department, count(*)
FROM employees
GROUP BY role, department;

   role    │ department │ count
───────────┼────────────┼───────
 Developer │ Sales      │     2
 Manager   │ Sales      │     1
 Manager   │ R&D        │     1
 Developer │ R&D        │     2

同じ情報でも、ピボットテーブルを使用した方が見やすいでしょう。psqlでは、\crosstabviewコマンドを使用することで、前回のクエリの結果をピボットテーブルに変換できます。

db=# \crosstabview

   role    │ Sales │ R&D
───────────┼───────┼─────
 Developer │     2 │   2
 Manager   │     1 │   1

この通り！

このコマンドは、デフォルトで最初の2つの列からピボットテーブルを作成しますが、これは引数を使用することで制御できます。

db=# \crosstabview department role

 department │ Developer │ Manager
────────────┼───────────┼─────────
 Sales      │         2 │       1
 R&D        │         2 │       1

利便性の面では若干劣りますが、あらかじめ組み込まれているtablefunc拡張機能を使用してピボットテーブルを作成する方法もあります。

db=# CREATE EXTENSION tablefunc;
CREATE EXTENSION

db=# SELECT * FROM crosstab('
    SELECT role, department, count(*) AS employees
    FROM employees
    GROUP BY 1, 2
    ORDER BY role
', '
    SELECT DISTINCT department
    FROM employees
    ORDER BY 1
') AS t(role text, sales int, rnd int);

   role    │ sales │ rnd
───────────┼───────┼─────
 Developer │     2 │   2
 Manager   │     1 │   1

crosstab関数を使用することで、ピボットテーブルを作成できます。この方法の欠点は、出力列をあらかじめ定義しておく必要があることです。しかし、crosstab関数が作成したテーブルをサブクエリとして使用し、さらなる処理を行えることは利点です。

ドル引用

データベースにテキストフィールド、特にパラグラフ全体を格納している場合、おそらくエスケープ文字についてはよくご存じでしょう。例えば、文字列リテラルにシングルクォート（'）を含める場合、もう1つシングルクォート（''）を使用してエスケープする必要があります。

db=# SELECT 'John''s Pizza';
   ?column?
──────────────
 John's Pizza

テキストの規模が大きくなり、バックスラッシュなどの文字や新しい行が含まれるようになると、エスケープ文字を追加するのがかなり面倒になってきます。この問題に対処するため、PostgreSQLでは文字列定数を記述する方法が他にも用意されています。

db=# SELECT $$a long
string with new lines
and 'single quotes'
and "double quotes

PostgreSQL doesn't mind ;)$$ AS text;
           text
───────────────────────────
 a long                   ↵
 string with new lines    ↵
 and 'single quotes'      ↵
 and "double quotes       ↵
                          ↵
 PostgreSQL doesn't mind ;)

文字列の始めと終わりにあるドル記号$$に注目してください。$$の間にある文字は文字列として扱われます。PostgreSQLでは、これを「ドル引用」と呼んでいます。

これだけではありません。テキスト内で$$記号を使用する必要がある場合、タグを追加できるのですが、これが利便性を一層高めてくれるのです。以下の例をご覧ください。

db=# SELECT $JSON${
    "name": "John's Pizza",
    "tagline": "Best value for your $$"
}$JSON$ AS json;

                  json
─────────────────────────────────────────
 {                                      ↵
     "name": "John's Pizza",            ↵
     "tagline": "Best value for your $$"↵
 }

このブロックでは、$JSON$というタグを付けているため、出力には「$$」記号全体が含まれています。

また、これを使用して特殊文字を含むjsonbオブジェクトを素早く生成することもできます。

db=# SELECT $JSON${
    "name": "John's Pizza",
    "tagline": "Best value for your $$"
}$JSON$::jsonb AS json;
                          json
─────────────────────────────────────────────────────────────
 {"name": "John's Pizza", "tagline": "Best value for your $$"}

値はjsonbオブジェクトとなり、意のままに操作できます。

データベースオブジェクトに関するコメント

PostgreSQLには、ほぼすべてのデータベースオブジェクトについてコメントを追加できる便利な機能があります。以下の例では、テーブルに関するコメントを追加しています。

db=# COMMENT ON TABLE sale IS 'Sales made in the system';
COMMENT

このコメントは、psql（およびおそらく他のIDE）で表示できます。

db=# \dt+ sale
                                  List of relations
 Schema │ Name │ Type  │ Owner │ Persistence │    Size    │       Description
────────┼──────┼───────┼───────┼─────────────┼────────────┼──────────────────────────
 public │ sale │ table │ haki  │ permanent   │ 8192 bytes │ Sales made in the system

テーブルの列に関するコメントを追加し、拡張記述を使用する際に表示できます。

db=# COMMENT ON COLUMN sale.sold_at IS 'When was the sale finalized';
COMMENT

db=# \d+ sale
  Column  │           Type           │         Description
──────────┼──────────────────────────┼─────────────────────────────
 id       │ integer                  │
 sold_at │ timestamp with time zone │ When was the sale finalized
 amount   │ integer                  │

また、COMMENTコマンドとドル引用を組み合わせて、関数などのより長く意義のある記述を含めることができます。

COMMENT ON FUNCTION generate_random_string IS $docstring$
Generate a random string at a given length from a list of possible characters.

Parameters:

    - length (int): length of the output string
    - characters (text): possible characters to choose from

Example:

    db=# SELECT generate_random_string(10);
     generate_random_string
    ────────────────────────
     o0QsrMYRvp

    db=# SELECT generate_random_string(3, 'AB');
     generate_random_string
    ────────────────────────
     ABB
$docstring$;

この関数は、筆者が過去に中規模のテキストがパフォーマンスに与える影響を実証するために使用したものです。コメントにdocstringを記述しているため、関数の使い方を思い出すために再び記事を見返す必要はもうありません。

db=# \df+ generate_random_string
List of functions
────────────┬────────────────────────────────────────────────────────────────────────────────
Schema      │ public
Name        │ generate_random_string
/* ... */
Description │ Generate a random string at a given length from a list of possible characters.↵
            │                                                                               ↵
            │ Parameters:                                                                   ↵
            │                                                                               ↵
            │     - length (int): length of the output string                               ↵
            │     - characters (text): possible characters to choose from                   ↵
            │                                                                               ↵
            │ Example:                                                                      ↵
            │                                                                               ↵
            │     db=# SELECT generate_random_string(10);                                   ↵
            │      generate_random_string                                                   ↵
            │     ────────────────────────                                                  ↵
            │      o0QsrMYRvp                                                               ↵
            │                                                                               ↵
            │     db=# SELECT generate_random_string(3, 'AB');                              ↵
            │      generate_random_string                                                   ↵
            │     ────────────────────────                                                  ↵
            │      ABB                                                                      ↵
            │

データベースごとに個別の履歴ファイルを維持する

CLIツールを使用している場合、過去のコマンドを検索する機能をかなり頻繁に使用しているのではないでしょうか。bashとpsqlでは、CTRL + Rを押すことで通常逆引きが可能です。

ターミナルの他に複数のデータベースで作業する場合、データベースごとに個別の履歴ファイルを維持すると便利かもしれません。

db=# \set HISTFILE ~/.psql_history- :DBNAME

そうすることで、現在接続しているデータベースに関連するマッチを見つけやすくなります。このファイルは、~/.psqlrc fileに置くことで永続化できます。

大文字の予約語の自動補完

SQLのキーワードを小文字で記述すべきか、それとも大文字で記述すべきかについては常に多くの議論（と冗談）の的になっています。このテーマに関する筆者の意見は明白です。

筆者のようにSQLで大文字のキーワードを使用するのを好む場合、大文字のキーワードを自動補完するオプションがpsqlに用意されています。

db=# selec <tab>
db=# select

db=# \set COMP_KEYWORD_CASE upper
db=# selec <tab>
db=# SELECT

COMPKEYWORDCASEをupperに設定すると、TABキーを押すことでキーワードが大文字で自動補完されるようになります。

スリープ時間の指定

プログラムの実行を遅らせることは、テストやスロットリングなどにおいてかなり有益な場合があります。PostgreSQLでプログラムの実行を遅らせるには、通常pg_sleep関数を使用します。

db=# \timing
Timing is on.

db=# SELECT pg_sleep(3);
 pg_sleep
──────────

(1 row)

Time: 3014.913 ms (00:03.015)

この関数は、指定した秒数スリープします。しかし、長い時間スリープする必要がある場合、秒数の計算が煩わしくなることもあります。以下の例をご覧ください。

db=# SELECT pg_sleep(255);

この関数がどれだけの時間スリープするか分かりますか？計算機を取り出す必要はありません。4分15秒です。長い時間スリープする場合の利便性向上のため、PostgreSQLでは別の関数が用意されています。

db=# SELECT pg_sleep_for('4 minutes 15 seconds');

姉妹関数のpgsleepとは異なり、[pgsleep_for](https://www.postgresql.org/docs/current/functions-datetime.html#FUNCTIONS-DATETIME-DELAY)関数は時間間隔（interval）での設定が可能であり、秒数よりもはるかに読みやすく、直感的に理解できます。

サブクエリを使用せず、グループの最初または最後の行を取得する

筆者はこの機能をいつも使用しているため、最初にこのリストを作成したときは、これを「あまり知られていない機能」だとは考えていませんでした。しかし、意外にもこの問題に対して奇妙な解決策が提示されていることが多く、これからお見せする方法で簡単に解決できるため、リストに加えることにしました。以下のような生徒テーブルがあるとします。

db=# SELECT * FROM students;

  name  │ class │ height
────────┼───────┼────────
 Haki   │ A     │    186
 Dan    │ A     │    175
 Jax    │ A     │    182
 George │ B     │    178
 Bill   │ B     │    167
 David  │ B     │    178

⚙テーブルデータ

このセクションのクエリーを再現するために、以下のCTEを使用することができます。

WITH students AS (
    SELECT * FROM (VALUES
        ('Haki',    'A',    186),
        ('Dan',     'A',    175),
        ('Jax',     'A',    182),
        ('George',  'B',    178),
        ('Bill',    'B',    167),
        ('David',   'B',    178)
    ) AS t(
        name,       class,  height
    )
)
SELECT * FROM students;

各クラスで一番背の高い生徒の行全体を取得するにはどうすればよいでしょうか?

最初に思いつく方法は次のようなものではないでしょうか。

SELECT class, max(height) as tallest
FROM students
GROUP BY class;

 class │ tallest
───────┼─────────
 A     │     186
 B     │     178

これだと身長は取得できますが、生徒の名前は得られません。次に、サブクエリを使用して、身長をもとに一番背の高い生徒を見つけようとするかもしれません。

SELECT *
FROM students
WHERE (class, height) IN (
    SELECT class, max(height) as tallest
    FROM students
    GROUP BY class
);

  name  │ class │ height
────────┼───────┼────────
 Haki   │ A     │    186
 George │ B     │    178
 David  │ B     │    178

これで、各クラスで一番背の高い生徒に関する情報がすべて揃いましたが、まだ別の問題があります。

追記

前のクエリ（(class, height) IN (...)）のようにレコードのセットをマッチングする機能もあまり知られていない、非常に強力なPostgreSQLの機能です。

クラス「B」には同じ身長の生徒が2人いますが、これがクラスで一番高い身長でもあります。集計関数MAXを使用すると身長しか得られないため、このような状況に直面する可能性があります。

MAXの使用が難しいのは、身長だけをもとに身長を選ばなくてはならないことであり、この場合は全く理にかなっているのですが、生徒を1人だけ選ぶ必要があります。2つ以上の列をもとに行を「ランク付け」できる別のアプローチとして、ウィンドウ関数を使用する方法があります。

SELECT
    students.*,
    ROW_NUMBER() OVER (
        PARTITION BY class
        ORDER BY height DESC, name
    ) AS rn
FROM
    students;

  name  │ class │ height │ rn
────────┼───────┼────────┼────
 Haki   │ A     │    186 │  1
 Jax    │ A     │    182 │  2
 Dan    │ A     │    175 │  3
 David  │ B     │    178 │  1
 George │ B     │    178 │  2
 Bill   │ B     │    167 │  3

身長をもとに生徒を「ランク付け」するには、各行に行番号を振ることができます。行番号はクラスごとに決められ（PARTITION BY class）まず身長の高い順にランク付けされ、次に生徒の名前でランク付けされます（ORDER BY height DESC, name）。身長の他に生徒の名前も追加することで、決定性のある結果が得られます（同じ名前がないと仮定した場合）。

各クラスで一番背の高い生徒の行だけを取得するには、サブクエリを使用できます。

SELECT
    name, class, height
FROM (
    SELECT
        students.*,
        ROW_NUMBER() OVER (
            PARTITION BY class
            ORDER BY height DESC, name
        ) AS rn
    FROM
        students
) as inner
WHERE
    rn = 1;

 name  │ class │ height
───────┼───────┼────────
 Haki  │ A     │    186
 David │ B     │    178

できました！これが各クラスで一番背の高い生徒の行全体です。

DISTINCT ONを使用する

かなり手間のかかる方法を見てきましたが、次はもっと簡単な方法を紹介したいと思います。

SELECT DISTINCT ON (class)
    *
FROM
    students
ORDER BY
    class, height DESC, name;

 name  │ class │ height
───────┼───────┼────────
 Haki  │ A     │    186
 David │ B     │    178

いかがでしょうか？最初にDISTINCT ONを発見したときは、とても感動しました。Oracleから来たものですが、他に同じような機能はなく、筆者が知る限りでは、PostgreSQL以外のデータベースに同様の機能はありません。

DISTINCT ONを直感的に理解する

DISTINCT ONの仕組みを理解するために、その挙動について順を追って見ていきましょう。以下がテーブル上の生データです。

SELECT *
FROM students;

  name  │ class │ height
────────┼───────┼────────
 Haki   │ A     │    186
 Dan    │ A     │    175
 Jax    │ A     │    182
 George │ B     │    178
 Bill   │ B     │    167
 David  │ B     │    178

次に、データを並べ替えます。

SELECT *
FROM students
ORDER BY class, height DESC, name;

  name  │ class │ height
────────┼───────┼────────
 Haki   │ A     │    186
 Jax    │ A     │    182
 Dan    │ A     │    175
 David  │ B     │    178
 George │ B     │    178
 Bill   │ B     │    167

DISTINCT ON句を追加します。

SELECT DISTINCT ON (class) *
FROM students
ORDER BY class, height DESC, name;

DISTINCT ONがこの時点で何をするかを理解するには、2つのステップを経なくてはいけません。

まず、DISTINCT ON句の中の列（この場合はclass）をもとに、データをグループに分けます。

  name  │ class │ height
─────────────────────────
 Haki   │ A     │    186  ┓
 Jax    │ A     │    182  ┣━━ class=A
 Dan    │ A     │    175  ┛

 David  │ B     │    178  ┓
 George │ B     │    178  ┣━━ class=B
 Bill   │ B     │    167  ┛

次に、各グループの1行目だけを残します。

 name  │ class │ height
─────────────────────────
 Haki   │ A     │    186  ┣━━ class=A
 David  │ B     │    178  ┣━━ class=B

これで終わりです。各クラスで一番背の高い生徒が得られました。

DISTINCT ONを使う上での唯一の要件は、ORDER BY句の先行列がDISTINCT ON句の列と一致することです。ORDER BY句の残りの列は、各グループのどの行を選択するかを判断するために使用します。

ORDER BYが結果にどう影響するかを理解するには、各クラスで一番背の低い生徒を特定する次のクエリを検討してください。

SELECT DISTINCT ON (class)
    *
FROM
    students
ORDER BY
    class, height, name;

 name │ class │ height
──────┼───────┼────────
 Dan  │ A     │    175
 Bill │ B     │    167

各クラスで一番背の低い生徒を選ぶには、各グループの1行目が一番背の低い生徒になるよう並び順を変えるだけでいいのです。

拡張機能なしでUUIDを生成する

PostgreSQLのバージョン13より前では、おそらく拡張機能uuid-osspを使用してUUIDを生成していたのではないでしょうか。

db=# CREATE EXTENSION "uuid-ossp";
CREATE EXTENSION

db=# SELECT uuid_generate_v4() AS uuid;
                 uuid
──────────────────────────────────────
 8e55146d-0ce5-40ab-a346-5dbd466ff5f2

バージョン13以降、ランダムなUUID（バージョン4）を生成するための関数が組み込まれています。

db=# SELECT gen_random_uuid() AS uuid;
                 uuid
──────────────────────────────────────
 ba1ac0f5-5d4d-4d80-974d-521dbdcca2b2

拡張機能uuid-osspは、バージョン4以外のUUIDを生成したい場合はまだ必要です。

再現可能なランダムデータの生成

ランダムデータの生成は、デモやテストなどさまざまな場面で非常に役立ちます。いずれの場合も、「ランダム」データを再現できると有用です。

PostgreSQLのrandom関数を使用することで、さまざまなタイプのランダムデータを生成できます。以下の例をご覧ください。

db=# SELECT
    random() AS random_float,
    ceil(random() * 10) AS random_int_0_10,
    '2022-01-01'::date + interval '1 days' * ceil(random() * 365) AS random_day_in_2022;

─[ RECORD 1 ]──────┬────────────────────
random_float       │ 0.6031888056092001
random_int_0_10    │ 3
random_day_in_2022 │ 2022-11-10 00:00:00

このクエリを再度実行すると、異なる結果が得られます。

db=# SELECT
    random() AS random_float,
    ceil(random() * 10) AS random_int_0_10,
    '2022-01-01'::date + interval '1 days' * ceil(random() * 365) AS random_day_in_2022;

─[ RECORD 1 ]──────┬────────────────────
random_float       │ 0.7363406030115378
random_int_0_10    │ 2
random_day_in_2022 │ 2022-02-23 00:00:00

再現可能なランダムデータを生成するには、setseedを使用できます。

db=# SELECT setseed(0.4050);
 setseed
─────────

(1 row)

db=# SELECT
    random() AS random_float,
    ceil(random() * 10) AS random_int_0_10,
    '2022-01-01'::date + interval '1 days' * ceil(random() * 365) AS random_day_in_2022
FROM
    generate_series(1, 2);

    random_float    │ random_int_0_10 │ random_day_in_2022
────────────────────┼─────────────────┼─────────────────────
 0.1924247516794324 │               9 │ 2022-12-17 00:00:00
 0.9720620908236377 │               5 │ 2022-06-13 00:00:00

新しいセッションで再び同じブロックを実行すると、異なるデータベースであっても全く同じ結果が得られます。

otherdb=# SELECT setseed(0.4050);
 setseed
─────────

(1 row)

otherdb=# SELECT
    random() AS random_float,
    ceil(random() * 10) AS random_int_0_10,
    '2022-01-01'::date + interval '1 days' * ceil(random() * 365) AS random_day_in_2022
FROM
    generate_series(1, 2);

    random_float    │ random_int_0_10 │ random_day_in_2022
────────────────────┼─────────────────┼─────────────────────
 0.1924247516794324 │               9 │ 2022-12-17 00:00:00
 0.9720620908236377 │               5 │ 2022-06-13 00:00:00

結果はランダムであるものの、全く同じであることに注目してください。次にデモを行ったり、スクリプトを共有したりする場合、結果を簡単に再現できるようsetseedを使用してみてください。

直ちに検証することなく制約を追加する

制約はRDBMSに不可欠な要素です。制約はデータをクリーンで信頼できる状態に保つため、なるべく使用することが望まれます。本番稼働中のシステムでは、新たな制約を追加しなくてはならないことがしばしばありますが、制約の種類によっては、システムの動作を妨げる非常に制限の厳しいロックが必要になることもあります。

例として、大規模なテーブルに単純なチェック制約を追加してみてください。

db=# ALTER TABLE orders ADD CONSTRAINT check_price_gt_zero CHECK (price >= 0);
ALTER TABLE
Time: 10745.662 ms (00:10.746)

この文は、注文の価格が0以上になるようチェック制約を追加します。制約を追加する際、データベースがテーブル全体をスキャンし、制約が既存のすべての行に対して有効であることを確認しました。このプロセスには約10秒かかり、その間テーブルはロックされました。

PostgreSQLでは、制約を追加するプロセスを2つのステップに分けることができます。

まず、制約を追加して新しいデータのみを検証し、既存のデータについては有効かどうかをチェックしません。

db=# ALTER TABLE orders ADD CONSTRAINT check_price_gt_zero CHECK (price >= 0) NOT VALID;
ALTER TABLE
Time: 13.590 ms

最後のNOT VALIDは、既存の行について新しい制約の検証を行わないようPostgreSQLに指示するものです。これは、データベースがテーブル全体をスキャンする必要がないことを意味します。この文が前の文に比べて所要時間が大幅に短く、ほぼ瞬時に処理されたことに注目してください。

次に、既存のデータについて、はるかに制限が緩く、テーブル上の他の演算を許容するロックを使用し、制約を検証します。

db=# ALTER TABLE orders VALIDATE CONSTRAINT check_price_gt_zero;
ALTER TABLE
Time: 11231.189 ms (00:11.231)

制約の検証に要した時間が、制約の追加と検証を行った最初の例とおおむね同じであることにお気づきでしょうか。これは、既存のテーブルに制約を追加する際、ほとんどの時間が既存の行の検証に費やされていることを再確認させるものです。プロセスを2つのステップに分けることで、テーブルがロックされている時間を短縮できます。

ドキュメントは、NOT VALIDの別のユースケースについても言及しています。それは、たとえ好ましくない値があったとしても、将来のアップデートに対してのみ制約を適用するというものです。つまり、NOT VALIDを追加し、VALIDATEは行わないということです。

Paypalのエンジニアリングチームがダウンタイムなしでスキーマに変更を加える方法について素晴らしい記事を執筆しています。バルクロードを行う際に制約とインデックスを無効化することを提案した筆者の記事も併せてご覧ください。

PostgreSQLにおけるシノニム

シノニム（同義語）は、オブジェクトを別の名前で参照する手段を提供するもので、Linuxのシンボリックリンクと似ています。Oracleの使用経験がある方はシノニムになじみがあるかと思われますが、そうでなければ聞いたことがないかもしれません。PostgreSQLには「シノニム」という名前の機能はありませんが、同様のことができないわけではありません。

特定の名前に別のデータベースオブジェクトを参照させるには、まず、PostgreSQLが条件を持たない名前をどのように解決するのか理解する必要があります。例えば、ユーザhakiとしてデータベースに接続し、テーブルfooを参照した場合、PostgreSQLは以下のオブジェクトを、以下の順序で検索します。

haki.foo
public.foo

この順序はsearch_pathパラメータによって決まります。

db=# SHOW search_path;
   search_path
─────────────────
 "$user", public

最初の値"$user"は特殊な値であり、現在接続しているユーザの名前に解決されます。2つ目の値publicはデフォルトスキーマの名前です。

サーチパスに関してできることを実証するため、データベースdbにテーブルfooを作成してみてください。

db=# CREATE TABLE foo (value TEXT);
CREATE TABLE

db=# INSERT INTO foo VALUES ('A');
INSERT 0 1

db=# SELECT * FROM foo;
 value
───────
 A
(1 row)

何らかの理由により、ユーザhakiがfooという名前を参照した際に異なるオブジェクトを表示させたい場合、2つの選択肢があります。

1. hakiという名前のスキーマにfooという名前のオブジェクトを作成する。

db=# CREATE SCHEMA haki;
CREATE SCHEMA

db=# CREATE TABLE haki.foo (value text);
CREATE TABLE

db=# INSERT INTO haki.foo VALUES ('B');
INSERT 0 1

db=# \conninfo
You are connected to database "db" as user "haki"

db=# SELECT * FROM foo;
value
───────
B

ユーザhakiがfooという名前を参照すると、PostgreSQLがpublic.fooではなくhaki.fooに名前を解決することに注目してください。これは、サーチパス上でスキーマhakiがpublicよりも前に来るためです。

2.サーチパスのアップデート。

db=# CREATE SCHEMA synonyms;
CREATE SCHEMA

db=# CREATE TABLE synonyms.foo (value text);
CREATE TABLE

db=# INSERT INTO synonyms.foo VALUES ('C');
INSERT 0 1

db=# SHOW search_path;
   search_path
─────────────────
 "$user", public

db=# SELECT * FROM foo;
 value
───────
 A

db=# SET search_path TO synonyms, "$user", public;
SET

db=# SELECT * FROM foo;
 value
───────
 C

サーチパスにスキーマsynonymsが含まれるよう変更を加えると、PostgreSQLがfooという名前をsynonyms.fooに解決するようになります。

シノニムはなぜ有用か？

筆者は以前、シノニムは避けるべきコードの臭いだと考えていましたが、その後シノニムが有用なユースケースをいくつか発見しました。その1つは、ゼロダウンタイムの移行です。

本番稼働中のシステムのテーブルに変更を加える際、アプリケーションの新旧バージョン両方を同時にサポートしなくてはならないことが多々あります。これは、アプリケーションの各バージョンが想定するテーブル構造が異なるため、困難を伴います。

テーブルから特定の列を削除する移行を例に検討してみましょう。移行が実行されている間、アプリケーションの古いバージョンは稼働しており、テーブルにその列があることを想定しているため、単純に削除することはできません。この問題に対処する1つの方法として、新しいバージョンを2段階に分けてリリースできます。1つ目のリリースではその列を無視し、2つ目のリリースで削除するのです。

ただし、1回のリリースで変更を行う必要がある場合、その列を含むテーブルのビューを古いバージョンに提供してから削除できます。これには「シノニム」を使用できます。

db=# \conninfo
You are now connected to database "db" as user "app".

db=# SELECT * FROM users;
 username │ active
──────────┼────────
 haki     │ t

アプリケーションは、ユーザappとしてデータベースdbに接続しています。active列を削除したいのですが、アプリケーションがこの列を使用しています。移行を無事完了するためには、古いバージョンが稼働している間、その列がまだそこにあるとユーザappに思わせる必要があります。

db=# \conninfo
You are now connected to database "db" as user "admin".

db=# CREATE SCHEMA app;
CREATE SCHEMA

db=# GRANT USAGE ON SCHEMA app TO app;
GRANT

db=# CREATE VIEW app.users AS SELECT username, true AS active FROM public.users;
CREATE VIEW

db=# GRANT SELECT ON app.users TO app;
GRANT

ユーザappを「騙す」ために、ユーザの名前を用いたスキーマと、計算済みフィールドactiveを持つビューを作成しました。アプリケーションがユーザappとして接続しているとき、テーブルではなくそのビューが見えるため、列を削除しても問題ありません。

db=# \conninfo
You are now connected to database "db" as user "admin".

db=# ALTER TABLE users DROP COLUMN active;
ALTER TABLE

db=# \connect db app
You are now connected to database "db" as user "app".

db=# SELECT * FROM users;
 username │ active
──────────┼────────
 haki     │ t

列を削除すると、アプリケーションは代わりに計算済みフィールドを参照します。後は少しクリーンアップを行って終了です。

重複レンジを探す

以下のような会議テーブルがあるとします。

db=# SELECT * FROM meetings;
       starts_at     │        ends_at
─────────────────────┼─────────────────────
 2021-10-01 10:00:00 │ 2021-10-01 10:30:00
 2021-10-01 11:15:00 │ 2021-10-01 12:00:00
 2021-10-01 12:30:00 │ 2021-10-01 12:45:00

⚙テーブルデータ

以下のCTEを使用して、このセクションのクエリーを再現することができます。

WITH meetings AS (
    SELECT
        starts_at::timestamptz AS starts_at,
        ends_at::timestamptz AS ends_at
    FROM (VALUES
        ('2021-10-01 10:00 UTC', '2021-10-01 10:30 UTC'),
        ('2021-10-01 11:15 UTC', '2021-10-01 12:00 UTC'),
        ('2021-10-01 12:30 UTC', '2021-10-01 12:45 UTC')
    ) AS t(
        starts_at,               ends_at)
)
SELECT * FROM meetings;

新しい会議の予定を入れたいのですが、その前に、他の会議と重ならないことを確認する必要があります。いくつか検討すべきシナリオがあります。

[A] 新しい会議は既存の会議が開始した後終了する。

|-------NEW MEETING--------|
    |--------*EXISTING MEETING--------*|

[B] 新しい会議は既存の会議が終わる前に開始する。

        |-------NEW MEETING--------|
|--------*EXISTING MEETING--------*|

[C] 新しい会議は既存の会議が実施されている間に行われる。

   |----NEW MEETING----|
|--------*EXISTING MEETING--------*|

[D] 既存の会議は新しい会議が予定されている時間内に行われる。

|--------NEW MEETING--------|
    |**EXISTING MEETING**|

[E] 新しい会議は既存の会議と全く同じ時間に予定されている。

|--------NEW MEETING--------|
|----**EXISTING MEETING--------|

重複の有無をチェックするクエリをテストするため、上記のシナリオがすべて含まれるテーブルを作成し、以下の簡単な条件式を試してみてください。

WITH new_meetings AS (
    SELECT
        id,
        starts_at::timestamptz as starts_at,
        ends_at::timestamptz as ends_at
    FROM (VALUES
        ('A', '2021-10-01 11:10 UTC', '2021-10-01 11:55 UTC'),
        ('B', '2021-10-01 11:20 UTC', '2021-10-01 12:05 UTC'),
        ('C', '2021-10-01 11:20 UTC', '2021-10-01 11:55 UTC'),
        ('D', '2021-10-01 11:10 UTC', '2021-10-01 12:05 UTC'),
        ('E', '2021-10-01 11:15 UTC', '2021-10-01 12:00 UTC')
    ) as t(
        id,   starts_at,               ends_at
    )
)
SELECT
    *
FROM
    meetings, new_meetings
WHERE
    new_meetings.starts_at BETWEEN meetings.starts_at and meetings.ends_at
    OR new_meetings.ends_at BETWEEN meetings.starts_at and meetings.ends_at;

       starts_at     │        ends_at      │ id │       starts_at     │        ends_at
─────────────────────┼─────────────────────┼────┼─────────────────────┼────────────────────
 2021-10-01 11:15:00 │ 2021-10-01 12:00:00 │ A  │ 2021-10-01 11:10:00 │ 2021-10-01 11:55:00
 2021-10-01 11:15:00 │ 2021-10-01 12:00:00 │ B  │ 2021-10-01 11:20:00 │ 2021-10-01 12:05:00
 2021-10-01 11:15:00 │ 2021-10-01 12:00:00 │ C  │ 2021-10-01 11:20:00 │ 2021-10-01 11:55:00
 2021-10-01 11:15:00 │ 2021-10-01 12:00:00 │ E  │ 2021-10-01 11:15:00 │ 2021-10-01 12:00:00

最初の試みでは、5 つのシナリオのうち 4 つのシナリオで重複を発見しました。シナリオDでは、新しい会議が既存の会議の前に始まり、後に終わる場合、重複を検出しませんでした。このシナリオも処理するためには、条件をもう少し長くする必要があります。

WITH new_meetings AS (/* ... */)
SELECT
    *
FROM
    meetings, new_meetings
WHERE
    new_meetings.starts_at BETWEEN meetings.starts_at and meetings.ends_at
    OR new_meetings.ends_at BETWEEN meetings.starts_at and meetings.ends_at
    OR meetings.starts_at BETWEEN new_meetings.starts_at and new_meetings.ends_at
    OR meetings.ends_at BETWEEN new_meetings.starts_at and new_meetings.ends_at;


       starts_at     │        ends_at      │ id │       starts_at     │        ends_at
─────────────────────┼─────────────────────┼────┼─────────────────────┼────────────────────
 2021-10-01 11:15:00 │ 2021-10-01 12:00:00 │ A  │ 2021-10-01 11:10:00 │ 2021-10-01 11:55:00
 2021-10-01 11:15:00 │ 2021-10-01 12:00:00 │ B  │ 2021-10-01 11:20:00 │ 2021-10-01 12:05:00
 2021-10-01 11:15:00 │ 2021-10-01 12:00:00 │ C  │ 2021-10-01 11:20:00 │ 2021-10-01 11:55:00
 2021-10-01 11:15:00 │ 2021-10-01 12:00:00 │ D  │ 2021-10-01 11:10:00 │ 2021-10-01 12:05:00
 2021-10-01 11:15:00 │ 2021-10-01 12:00:00 │ E  │ 2021-10-01 11:15:00 │ 2021-10-01 12:00:00

このクエリは5つすべてのシナリオで重複を検出しますが、以下の追加シナリオを検討してください。

[F] 新しい会議は既存の会議の直後に予定されている。

                            |--------NEW MEETING--------|
|----**EXISTING MEETING--------|

[G] 新しい会議は既存の会議の開始直後に終了する予定になっている。

|--------NEW MEETING--------|
                            |----**EXISTING MEETING--------|

会議が連続するのはよくあることですが、これらについては重複と認識されるべきではありません。2つのシナリオをテストに追加し、クエリを実行してみます。

WITH new_meetings AS (
    SELECT
        id,
        starts_at::timestamptz as starts_at,
        ends_at::timestamptz as ends_at
    FROM (VALUES
        ('A', '2021-10-01 11:10 UTC', '2021-10-01 11:55 UTC'),
        ('B', '2021-10-01 11:20 UTC', '2021-10-01 12:05 UTC'),
        ('C', '2021-10-01 11:20 UTC', '2021-10-01 11:55 UTC'),
        ('D', '2021-10-01 11:10 UTC', '2021-10-01 12:05 UTC'),
        ('E', '2021-10-01 11:15 UTC', '2021-10-01 12:00 UTC'),
        ('F', '2021-10-01 12:00 UTC', '2021-10-01 12:10 UTC'),
        ('G', '2021-10-01 11:00 UTC', '2021-10-01 11:15 UTC')
    ) as t(
        id,   starts_at,               ends_at
    )
)
SELECT
    *
FROM
    meetings, new_meetings
WHERE
    new_meetings.starts_at BETWEEN meetings.starts_at and meetings.ends_at
    OR new_meetings.ends_at BETWEEN meetings.starts_at and meetings.ends_at
    OR meetings.starts_at BETWEEN new_meetings.starts_at and new_meetings.ends_at
    OR meetings.ends_at BETWEEN new_meetings.starts_at and new_meetings.ends_at;

       starts_at     │        ends_at      │ id │       starts_at     │        ends_at
─────────────────────┼─────────────────────┼────┼─────────────────────┼────────────────────
 2021-10-01 11:15:00 │ 2021-10-01 12:00:00 │ A  │ 2021-10-01 11:10:00 │ 2021-10-01 11:55:00
 2021-10-01 11:15:00 │ 2021-10-01 12:00:00 │ B  │ 2021-10-01 11:20:00 │ 2021-10-01 12:05:00
 2021-10-01 11:15:00 │ 2021-10-01 12:00:00 │ C  │ 2021-10-01 11:20:00 │ 2021-10-01 11:55:00
 2021-10-01 11:15:00 │ 2021-10-01 12:00:00 │ D  │ 2021-10-01 11:10:00 │ 2021-10-01 12:05:00
 2021-10-01 11:15:00 │ 2021-10-01 12:00:00 │ E  │ 2021-10-01 11:15:00 │ 2021-10-01 12:00:00
 2021-10-01 11:15:00 │ 2021-10-01 12:00:00 │ F  │ 2021-10-01 12:00:00 │ 2021-10-01 12:10:00
 2021-10-01 11:15:00 │ 2021-10-01 12:00:00 │ G  │ 2021-10-01 11:00:00 │ 2021-10-01 11:15:00

2つの会議が連続するシナリオFとGは、誤って重複に分類されています。これは、演算子BETWEENがその前後を含むためです。BETWEENを使用せずにこの条件式を実行するには、以下のようにする必要があります。

WITH new_meetings AS (/* ... */)
SELECT
    *
FROM
    meetings, new_meetings
WHERE
    (new_meetings.starts_at > meetings.starts_at AND new_meetings.starts_at < meetings.ends_at)
    OR
    (new_meetings.ends_at > meetings.starts_at AND new_meetings.ends_at < meetings.ends_at)
    OR
    (meetings.starts_at > new_meetings.starts_at AND meetings.starts_at < new_meetings.ends_at)
    OR
    (meetings.ends_at > new_meetings.starts_at AND meetings.ends_at < new_meetings.ends_at)
    OR
    (meetings.starts_at = new_meetings.starts_at AND meetings.ends_at = new_meetings.ends_at);

       starts_at     │        ends_at      │ id │       starts_at     │        ends_at
─────────────────────┼─────────────────────┼────┼─────────────────────┼────────────────────
 2021-10-01 11:15:00 │ 2021-10-01 12:00:00 │ A  │ 2021-10-01 11:10:00 │ 2021-10-01 11:55:00
 2021-10-01 11:15:00 │ 2021-10-01 12:00:00 │ B  │ 2021-10-01 11:20:00 │ 2021-10-01 12:05:00
 2021-10-01 11:15:00 │ 2021-10-01 12:00:00 │ C  │ 2021-10-01 11:20:00 │ 2021-10-01 11:55:00
 2021-10-01 11:15:00 │ 2021-10-01 12:00:00 │ D  │ 2021-10-01 11:10:00 │ 2021-10-01 12:05:00
 2021-10-01 11:15:00 │ 2021-10-01 12:00:00 │ E  │ 2021-10-01 11:15:00 │ 2021-10-01 12:00:00

クエリはシナリオA～Eを重複として正しく認識し、会議が連続するシナリオFとGは重複とは認識しません。求めていた結果はこれです。しかし、この条件式はあまりにも複雑です。容易に手に負えなくなってしまうでしょう。

そこで、PostgreSQLの以下の演算子が極めて有益となります。

WITH new_meetings AS (
    SELECT
        id,
        starts_at::timestamptz as starts_at,
        ends_at::timestamptz as ends_at
    FROM (VALUES
        ('A', '2021-10-01 11:10 UTC', '2021-10-01 11:55 UTC'),
        ('B', '2021-10-01 11:20 UTC', '2021-10-01 12:05 UTC'),
        ('C', '2021-10-01 11:20 UTC', '2021-10-01 11:55 UTC'),
        ('D', '2021-10-01 11:10 UTC', '2021-10-01 12:05 UTC'),
        ('E', '2021-10-01 11:15 UTC', '2021-10-01 12:00 UTC'),
        ('F', '2021-10-01 12:00 UTC', '2021-10-01 12:10 UTC'),
        ('G', '2021-10-01 11:00 UTC', '2021-10-01 11:15 UTC')
    ) as t(
        id,   starts_at,               ends_at
    )
)
SELECT
    *
FROM
    meetings, new_meetings
WHERE
    (new_meetings.starts_at, new_meetings.ends_at)
        OVERLAPS (meetings.starts_at, meetings.ends_at);

       starts_at     │        ends_at      │ id │       starts_at     │        ends_at
─────────────────────┼─────────────────────┼────┼─────────────────────┼────────────────────
 2021-10-01 11:15:00 │ 2021-10-01 12:00:00 │ A  │ 2021-10-01 11:10:00 │ 2021-10-01 11:55:00
 2021-10-01 11:15:00 │ 2021-10-01 12:00:00 │ B  │ 2021-10-01 11:20:00 │ 2021-10-01 12:05:00
 2021-10-01 11:15:00 │ 2021-10-01 12:00:00 │ C  │ 2021-10-01 11:20:00 │ 2021-10-01 11:55:00
 2021-10-01 11:15:00 │ 2021-10-01 12:00:00 │ D  │ 2021-10-01 11:10:00 │ 2021-10-01 12:05:00
 2021-10-01 11:15:00 │ 2021-10-01 12:00:00 │ E  │ 2021-10-01 11:15:00 │ 2021-10-01 12:00:00

これです！OVERLAPS演算子を使用することで、5つの複雑な条件式が不要になり、クエリは短く単純で、読みやすく、分かりやすくなりました。

改訂履歴

2021年11月9日：Redditのコメント投稿者が、「大文字の予約語の自動補完」のセクションでpsqlパラメータの名前の誤りを見つけてくれました。COMPKEYWORDUPPERをCOMPKEYWORDCASEに修正しました。
2021年11月9日：pgsleepの例は、記事の中で以前述べていたように4分ではなく4時間（14400秒）のスリープでした。pgsleep_forによる時間間隔を用いるメリットをより明確に伝えるため、例に変更を加えました。

あまり知られていないPostgreSQLの機能

目次

UPSERTにおいて更新、挿入された行数の取得

RETURNING *

特定の列の権限付与

複数パターンに対するマッチング

シーケンスを進めることなく現在値を確認する

複数行のSQLで\COPYを使用する

コピーの達人

自動生成キーの値の設定を防ぐ

ピボットテーブルを作成するもう2つの方法

ドル引用

データベースオブジェクトに関するコメント

データベースごとに個別の履歴ファイルを維持する

大文字の予約語の自動補完

スリープ時間の指定

サブクエリを使用せず、グループの最初または最後の行を取得する

追記

拡張機能なしでUUIDを生成する

再現可能なランダムデータの生成

直ちに検証することなく制約を追加する

PostgreSQLにおけるシノニム

重複レンジを探す