PostgreSQL のカラム型で普段の業務で使用するものは、数字型、文字列型、日付型がほとんどではないでしょうか。（私だけ？）
今回は私が普段使わない型をさらっと試してみることにしました。内容はポエムに近いです。PostgreSQL 17.9 (docker) で試しました。
1. ネットワーク型
postgres=# SELECT ip, cidr, ip << cidr AS is_contained
FROM (
VALUES
('10.0.0.1'::inet, '10.0.0.0/8'::cidr),
('192.168.0.5'::inet, '192.168.1.0/24'::cidr),
('192.168.0.5'::inet, '192.168.0.0/16'::cidr)
) AS t(ip, cidr);
ip | cidr | is_contained
-------------+----------------+--------------
10.0.0.1 | 10.0.0.0/8 | t
192.168.0.5 | 192.168.1.0/24 | f
192.168.0.5 | 192.168.0.0/16 | t
(3 行)ip アドレス型とcidr 型です。ip << cidr で含まれることを判定できます。ip アドレスを管理するシステムで使われていそうですね。
2. 図形型
四角(::box) に点(::point) が含まれるか判定できます。
postgres=# SELECT rect, p, p <@ rect AS is_contained
FROM (
VALUES
('(1,1),(5,5)'::box, '(2,2)'::point),
('(1,1),(5,5)'::box, '(1,1)'::point),
('(1,1),(5,5)'::box, '(2,6)'::point),
('(1,1),(5,5)'::box, '(6,2)'::point),
('(1,1),(5,5)'::box, '(1,0)'::point)
) AS t(rect, p);
rect | p | is_contained
-------------+-------+--------------
(5,5),(1,1) | (2,2) | t
(5,5),(1,1) | (1,1) | t
(5,5),(1,1) | (2,6) | f
(5,5),(1,1) | (6,2) | f
(5,5),(1,1) | (1,0) | f
(5 行)‘(1,1),(5,5)’::box で2点を頂点とする矩形を表します。point はそのまま座標ですね。
p <@ rect で矩形に含まれるか判定できました。
下の3点は四角の外側にある点であるため、ちゃんとfalse になっています。図面アプリなどで役に立つ気がします。
3. UUID
こちらは事前準備が必要です。拡張機能を有効にしておきます。
postgres=# CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
CREATE EXTENSIONpostgres=# select uuid_generate_v4();
uuid_generate_v4
--------------------------------------
27ba4664-bbbb-418d-afc5-ac928a144621
(1 行)文字列でよさそうなものですが、文字列として保存するより比較が高速で型安全になるメリットがあります。
あと、フォーマット不正の場合にエラーとなります。(末尾に余分な@を追加した)
postgres=# select '27ba4664-bbbb-418d-afc5-ac928a144621@'::uuid;
ERROR: "uuid"型の入力構文が不正です: "27ba4664-bbbb-418d-afc5-ac928a144621@"
行 1: select '27ba4664-bbbb-418d-afc5-ac928a144621@'::uuid;
^4. JSON
こちらは以前、私の隣のチームが使っているという話を聞いたことがあります。
JSON 型はテキストとして保存されますが、JSONB はバイナリ形式で保存され検索やインデックスが高速なため、JSONB 型を使うのが一般的らしいです。
postgres=# select p.id, p.x, (p.x #> '{user, name}' = '"user"') as is_user
FROM (
VALUES
(1, '{"user":{"name":"グリーン"}}'::jsonb),
(2, '{"user":{"name":"コード"}}'::jsonb),
(3, '{"user":{"name":"user"}}'::jsonb)
) AS p(id, x);
id | x | is_user
----+--------------------------------+---------
1 | {"user": {"name": "グリーン"}} | f
2 | {"user": {"name": "コード"}} | f
3 | {"user": {"name": "user"}} | t
(3 行)ちょっと無理やりな例ですが、like で難しそうな user.name が “user” のデータを識別できています。
p.x #> ‘{user, name}’ = ‘”user”‘ が、Javascript でいうと x.user.name == “user” の意味になります。
文字列型で格納した場合には難しそうなことができています。
また、JSON フォーマット不正も検知されます。
postgres=# select '{a:}'::jsonb;
ERROR: json型に対する不正な入力構文
行 1: select '{a:}'::jsonb;
^
DETAIL: トークン"a"は不正です。
CONTEXT: JSONデータ、1行目: {a...5. XML
JSON と同じように、構造化データとして扱うことができます。
postgres=# select p.id, p.x, (SELECT xpath_exists('/user[text()="user"]', p.x)) AS is_user
FROM (
VALUES
(1, '<user>グリーン</user>'::xml),
(2, '<user>コード</user>'::xml),
(3, '<user>user</user>'::xml)
) AS p(id, x);
id | x | is_user
----+-----------------------+---------
1 | <user>グリーン</user> | f
2 | <user>コード</user> | f
3 | <user>user</user> | t
(3 行)JSON の例と同様に、 要素の中のテキストが “user” のデータを識別できています。
フォーマット不正を検知できるのも一緒です。
こちらも文字列型より柔軟な検索ができますね。
ちなみに、データからXML を作る機能もあるようです。
postgres=# SELECT
XMLELEMENT(NAME user,
XMLATTRIBUTES(p.id AS id),
p.name
) AS catalog_xml
FROM (
VALUES
(1, 'user'),
(2, 'ユーザ'),
(3, 'アカウント')
) AS p(id, name);
catalog_xml
--------------------------------
<user id="1">user</user>
<user id="2">ユーザ</user>
<user id="3">アカウント</user>
(3 行)感想
普段は text や integer で済ませてしまいがちですが、専用の型を使うことで「DB層でのバリデーション」と「専用演算子による高速な検索」という大きな恩恵が得られることが分かりました。
さらに、いずれもインデックスを効かせることができるため、大量データとなった場合にこれらの機能がとても輝くのだと思います。
覚えておくと役に立つことがありそうです。
【広告】
コメントを残す