PostgreSQL の列はどれも決まった型を持ち、CREATE TABLE で指定します。ほとんどのテーブルで必要な型は少しだけです。整数には bigint か integer、金額には numeric、文字列には text、それに boolean、時点には timestamptz、ドキュメントには jsonb です。どの値の型も pg_typeof() で確認できます。
主な型と使いどころ
| 型 | 保持するもの | 用途 |
|---|---|---|
integer (int) | 約 21 億までの整数、4 バイト | 件数、数量、小さな id |
bigint | 約 922 京までの整数、8 バイト | 主キー、増え続けるもの |
smallint | 32767 まで、2 バイト | 使う価値はほとんどない |
numeric(p, s) | 正確な小数 | 金額、レート、合計するもの |
real、double precision | 2 進浮動小数点数 | 測定値、科学計算の値 |
text | 任意の長さの文字列 | 名前、メールアドレス、説明文 |
varchar(n) | 長さに上限のある文字列 | 上限がルールのとき |
boolean | true、false、NULL | フラグ |
date | 日付 | 誕生日、期日 |
timestamptz | 時点 | 作成日時と更新日時 |
interval | 期間 | '2 hours'、'30 days' |
uuid | 128 ビットの識別子 | 公開する id、分散環境の id |
jsonb | 解析済みの JSON | 形の決まっていないデータ |
text[]、int[] | 任意の型の配列 | 短いリスト、タグ |
bytea | 生のバイト列 | 小さなバイナリ値 |
大きな選択にはそれぞれ専用のページがあります: timestamp と timestamptz、enum 型、jsonb、配列。id (serial、identity 列、uuid) は主キーのページで扱います。
numeric と double precision
numeric は小数を正確に保存します。real と double precision は 2 進数の小数を保存するので、0.1 のような値は近似値になり、合計がずれることがあります。
numeric の合計は 0.3 です。浮動小数点数の合計は 0.30000000000000004 になり、等価の判定は false です。金額には numeric を使ってください。numeric(10, 2) は全体で 10 桁、小数点以下 2 桁を許し、それより多い小数は挿入時に丸めます。
整数を整数で割ると、小数部分が切り捨てられます。
7 / 2 は 3 を返します。3.5 を得るには、どちらかを numeric にします (小数点付きのリテラルにするか、キャストします)。
text、varchar(n)、char(n) の違い
3 つとも保存方法は同じで、どれかが速いということはありません。varchar(n) は挿入時に長すぎる値を拒否します。役に立つのは、その上限がデータのルールである場合だけです。char(n) はスペースで埋めるので、新しいテーブルで使う理由はありません。varchar(n) への明示的なキャストは、エラーにならずに切り詰めます。
キャストは Post を返します。char(5) の値は 5 バイトを使いますが、length は埋めたスペースを無視して 2 を返します。
boolean の値と NULL
boolean 列は true、false、't'、'yes'、'on'、'1' とその反対の値を受け付け、常に true か false を返します。値がない場合は NULL で、これはどちらでもありません。そのため WHERE NOT active は active が NULL の行を飛ばします。
NULL を false として扱いたいときは、IS TRUE、IS NOT TRUE、COALESCE(active, false) を使ってください。
よくある質問
PostgreSQL の text と varchar に性能の差はありますか?
ありません。text、varchar、varchar(n) は同じ方法で保存されます。varchar(n) は長さのチェックが加わるだけなので、その上限がデータの本当のルールでない限り text を使ってください。
PostgreSQL で money 型を使うべきですか?
たいていは使いません。入出力が lc_monetary の設定に左右され、値に通貨が記録されません。金額は numeric(12,2) に保存し、通貨は別の列に持たせてください。
PostgreSQL で DATETIME にあたる型は何ですか?
時点を表すなら timestamptz (timestamp with time zone)、タイムゾーンのない時計の時刻なら timestamp です。PostgreSQL に DATETIME 型はありません。
PostgreSQL で値や列の型を確認するには?
SELECT pg_typeof(expression) は、列を含むあらゆる式の型を返します。psql では \d table_name で、すべての列とその型が表示されます。