PostgreSQLのデータ型を「なんとなく」で選ばない

PostgreSQLでテーブルを作るとき、保存する値が決まっていても、データ型の選択に迷うことがあります。注文コードには、長さを指定できるvarchar(n)を使うのか、textでよいのか。金額にはnumericを使うのか、円単位なら整数型でよいのか。日時や外部APIのレスポンスにも、複数の保存方法があります。

テストデータを少し入れるだけなら、どの型でも問題なく動くように見えるかもしれません。しかし、注文コードの形式が変わったり、金額を集計したり、海外拠点の時刻を扱ったり、決済レスポンスを検索したりすると、型の違いが表面化します。型を選ぶときは、値を保存できるかだけでなく、保存後にどう使うかまで考えます。

この記事は、SQLの基本を理解し、これからテーブルを設計する方に向けた入門向け記事です。架空の注文管理テーブルordersを例に、文字列、数値、日付時刻、JSONの型を用途別に検討します。型選びに関わる性質と判断理由に絞って説明します。

例では、注文コード、単価、数量、注文日時などを保存します。まずテーブルを定義し、各列の型を用途に照らして確認します。

この最初の定義には、後で見直す候補としてdouble precisionとjsonをあえて含めています。以降の章で、金額の正確さやJSON検索の必要性に応じて、型を変更する条件を確認します。

CREATE TABLE orders (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY -- 注文を一意に識別するID。値はPostgreSQLが自動で採番する。
  , order_code varchar(16) NOT NULL                  -- 注文コード。最大16文字の文字列を必ず入れる。
  , customer_memo text                               -- 顧客からのメモ。業務上の長さ制限を設けず、入力は任意。
  , unit_price numeric(12, 2) NOT NULL               -- 商品1個あたりの単価。全体で12桁、小数点以下2桁。
  , quantity integer NOT NULL                        -- 注文数量。整数値を必ず入れる。
  , total_amount double precision NOT NULL           -- 注文金額の合計。必ず入れる。
  , ordered_at timestamp without time zone NOT NULL  -- 注文日時。必ず入れる。
  , payment_payload json                             -- 決済サービスなどから返されたJSON形式のデータ。
);

1. 文字列型は上限と条件の表し方で選ぶ

1.1. char(n)を選ぶ場面は限られる

注文コードのように形式が決まった文字列には、固定長のchar(n)も候補に見えます。ただし、char(n)は入力が指定した長さに満たない場合、末尾に空白を補います。また、char同士の比較では末尾の空白を無視します。そのため、末尾の空白を値の一部として区別したい列には向きません。

CREATE TEMP TABLE order_code_char (
  code char(5) NOT NULL
);

INSERT INTO order_code_char VALUES ('hi');

SELECT code, char_length(code), octet_length(code), code = 'hi   ', code = 'hi'
FROM order_code_char;
 code  | char_length | octet_length | ?column? | ?column?
-------+-------------+--------------+----------+----------
 hi    |           2 |            5 | t        | t
(1 row)

この例では、'hi'の末尾に3つの空白を補ってchar(5)の幅で保存しています。char_length(code)は末尾の空白を数えないため2を返しますが、octet_length(code)は、このASCII文字列では5を返します。空白を含む'hi 'との比較も、含まない'hi'との比較もtrueです。固定長を指定しても、入力された文字列をそのまま区別して保存できるわけではありません。

性能面でも、char(n)を選ぶ利点はありません。公式文書の「8.3. 文字型」によると、PostgreSQLのchar(n)には固有の性能上の利点がありません。空白埋めで保存領域が増えるため、char(n)、varchar(n)、textの中では通常最も遅い型です。新規設計ではchar(n)を避け、最大長を制限するならvarchar(n)やCHECK制約を検討します。

1.2. varchar(n)は文字数の上限を型に記せる

orders.order_codeには、最大16文字を保存できるvarchar(16)を指定しています。varchar(16)は、char(n)のように後ろに空白文字を補わず、指定した文字列をそのまま保存します。初期の注文コードが次の形式なら15文字なので、後ろに空白文字が付くことなく保存できます。

CREATE TEMP TABLE orders_code_varchar (
  order_code varchar(16) NOT NULL
);

INSERT INTO orders_code_varchar VALUES ('OD-000000000001');

発番側の仕様変更で注文コードに日付が加わると、上限を超える場合があります。次の値は18文字なので、同じ列には保存できません。

INSERT INTO orders_code_varchar VALUES ('OD-20260801-000001');
ERROR:  value too long for type character varying(16)

これはvarchar(n)の長さ制限によるエラーです。公式文書の「8.3. 文字型」では、character varying(n)、つまりvarchar(n)は最大n文字を保存できる型と説明されています。通常の挿入では、超過分がすべて空白の場合を除き、上限を超える入力はエラーになります。ここでいうnはバイト数ではなく文字数です。

varchar(n)を使うときは、現在のデータが収まるかに加えて、上限の根拠を確認します。注文コードの仕様が変わったら、発番側の変更に合わせてデータベース側の上限も見直します。

1.3. 長さの上限は型か制約で表す

注文コードの上限が業務仕様として「最大18文字」と決まっているなら、varchar(18)を使えます。型定義から最大長が分かり、上限を超える入力はデータベースが拒否します。上限を変える場合は、列の型定義を変更します。

文字列型と業務上の条件を分けて定義するなら、textとCHECK制約を組み合わせる方法もあります。CHECK制約は、条件式がFALSEになる値を拒否します。条件式がNULLになる値は通過するため、NULLも拒否する場合はNOT NULL制約を併用します。次の定義では、text型の注文コードをOD-で始まる18文字以下の値に制限しています。

CREATE TEMP TABLE orders_code_text (
  order_code text NOT NULL,
  CONSTRAINT orders_code_format_check CHECK (
    order_code LIKE 'OD-%'
    AND char_length(order_code) <= 18
  )
);

CHECK制約では、長さと接頭辞の条件を同時に定義できます。上限を変えるときは、列の型を維持して制約を変更します。長さだけを型に記すならvarchar(n)、長さを含む業務条件をまとめて管理するならtextとCHECK制約、というように条件の表し方で選べます（CHECK制約）。

この使い分けは、短い文字列にはvarchar(n)、長い文字列にはtextという性能上の区別ではありません。公式文書の「8.3. 文字型」によると、長さ制限の検査や空白埋めの負担を除けば、文字列型の間に性能差はありません。orders.customer_memoのように業務上の長さ制限がない列には、textを使えます。

1.4. 保存できる文字列でもB-treeインデックスに入らない場合がある

長い文字列を保存できることと、その値全体をインデックスのキーにできることは別です。たとえばorders.customer_memoをtextで保存できても、その列にB-treeインデックスを作成できるとは限りません。B-treeは等価比較や範囲検索などに使うインデックスですが、1件のインデックスエントリにはサイズ上限があります。

PostgreSQLは、テーブルやインデックスをページ（データを格納する単位）に分けて管理します。標準的なページサイズは8 KiBです。公式文書の「B-treeインデックス」によると、1つのインデックスエントリは、1ページのおよそ3分の1（8 KiBのページでは約2.7 KiB）を超えられません。TOASTによる圧縮が適用される場合は、圧縮後のサイズが制限の対象です。そのため、テーブルには保存できても、値全体をキーにするとINSERTやCREATE INDEXがエラーで失敗します。値を切り詰めて登録することはありません。

たとえば、圧縮されにくい文字列を保存してからB-treeインデックスを作成すると、次のようなエラーが発生します。エラーメッセージのbuffer pageは、上述のページを指しています。

CREATE TEMP TABLE long_memo (
  memo text
);

INSERT INTO long_memo
SELECT string_agg(md5(random()::text), '')
FROM generate_series(1, 200);

CREATE INDEX ON long_memo (memo);
ERROR:  index row size 6416 exceeds btree version 4 maximum 2704 for index "long_memo_memo_idx"
DETAIL:  Index row references tuple (0,1) in relation "long_memo".
HINT:  Values larger than 1/3 of a buffer page cannot be indexed.
Consider a function index of an MD5 hash of the value, or use full text indexing.

この例では、文字列自体はtext列に保存できますが、値全体をB-treeのキーにできないため、CREATE INDEXが失敗しています。既にインデックスがある場合は、同じ値をINSERTした時点でインデックスへの追加に失敗します。INSERT文はエラーで取り消されるため、テーブルに行だけが残ることもありません。この単一列のtextインデックスでは、サイズ制限を回避するために値を途中で切り詰めて登録することはありません。切り詰めた部分だけを使って、異なる値を同一視する動作もありません。

この制限はtextだけでなく、大きなvarchar(n)にも当てはまります。長い文字列を検索するときは、検索方法に合ったインデックスを選びます。単語単位の検索には全文検索とGINインデックス、完全一致検索にはハッシュ値を使った式インデックスなどが候補です。ハッシュ値には衝突の可能性があるため、利用者はハッシュ値の条件に加えて元の値との比較条件も検索に含めます。PostgreSQLは、指定された元の値との比較条件を評価し、ハッシュ値が一致した候補から検索対象を絞り込みます。

2. 数値型は必要な正確さと値の範囲で選ぶ

2.1. double precisionは2進数で表せる値しか正確に扱えない

金額の計算や比較を10進数の規則どおりに行うなら、double precisionは使いません。冒頭のorders.total_amountは、必要な桁数を指定したnumericに変更します。一方、測定値や統計計算のように近似値を許容し、計算速度や表現できる値の範囲を優先する用途では、double precisionを使えます。

double precisionは小数を扱えますが、2進数で有限桁に表せない値は近似値になります。税率や割引率を使って金額を計算し、小数点以下まで正確に扱う場合には、この性質が問題になります。

numericとdouble precisionの違いは、次の計算で確認できます。どちらも0.1 + 0.2が0.3と等しいかを比較しています。

SELECT 0.1::numeric + 0.2::numeric = 0.3::numeric AS numeric_equal;
 numeric_equal
---------------
 t
(1 row)

SELECT 0.1::double precision
     + 0.2::double precision = 0.3::double precision AS double_equal;
 double_equal
--------------
 f
(1 row)

numericではtrue、double precisionではfalseになります。0.1は2進数で有限桁に表せないため、double precisionでは近似値として扱われます。一方、numericは10進数の値を正確に扱うため、この計算では期待どおりの比較結果になります（浮動小数点数データ型）。

整数だけなら、double precisionでも−2の53乗から2の53乗までの整数をすべて正確に保存できます。正数の上限は約9,000兆です。この範囲の金額を1円単位で保存するだけなら、浮動小数点表現による誤差は生じません。また、0.5のように2進数で正確に表せる小数もあります。ただし、税率や割引率を使った計算には、0.1のように近似値になる小数が含まれることがあります。整数を正確に保存できても、その後の計算まで正確とは限りません。

0.1のように2進数で有限桁に表せない小数が計算に含まれると、その値は近似になります。そのため、金額を10進数の規則で計算・比較し、丸めを管理したい要件にはdouble precisionは向きません。orders.total_amountにはnumericを使います。

2.2. 金額にはnumeric、個数には整数型を使う

10進数として正確に扱う金額には、必要な桁数を決めてnumericを使います。numeric(precision, scale)のprecisionは全体の桁数、scaleは小数点以下の桁数です。orders.unit_priceのnumeric(12, 2)は、全体で12桁、小数点以下2桁を指定しています。指定より多い小数桁の入力は、まず2桁に丸められます。丸めた後の整数部が10桁を超える値は保存できません。orders.total_amountにも同じ考え方を使えますが、合計額に必要な桁数は単価とは分けて確認します。

金額を常に円単位の整数として扱うなら、integerやbigintに保存する設計も選べます。この場合も、税率や割引率の計算で小数が出たとき、どの時点で丸めるかを決めます。orders.quantityのような個数や回数には整数型を使い、想定する値の範囲に応じてintegerかbigintを選びます。

近似値を許容できる値には、double precisionを検討します。測定値や統計計算の結果など、10進数としての厳密な一致より計算速度や表現できる値の範囲を優先する用途です。小数を含むかどうかだけでなく、どの程度の正確さが要るかでnumericと使い分けます。

2.3. 新規設計ではmoney型よりnumericを優先する

金額を保存する型にはmoneyもありますが、新規設計ではnumericを優先します。moneyの表示形式や小数部の精度は、データベースのlc_monetary設定に依存します。設定が異なるデータベースへダンプをリストアすると、ダンプに含まれるmoneyの文字列表現をリストア先のlc_monetaryで解釈できず、読み込みに失敗することがあります。読み込みに成功しても、表示形式や小数部の扱いが変わる可能性があります。また、moneyを整数で割った結果は、表現できない小数部を0方向へ切り捨てます。負数の場合も、絶対値が小さくなる方向です。そのため、moneyはデータベースの設定と、演算に組み合わせる型の規則を意識して使う必要があります（通貨型）。

moneyは既存スキーマとの互換性など、明確な理由がある場合に検討します。通貨記号を含む表示が必要なら、numericの値をto_charで整形できます（データ型書式設定関数）。たとえば、to_char(unit_price, 'L999G999G999G990D00')で通貨記号、桁区切り、小数点以下の表示形式を指定できます。ordersの金額列では、保存したnumericの値を計算や比較に使い、画面に表示するときだけto_charで表示用の文字列に変換します。

3. 日付時刻型は保存したい時刻の意味で選ぶ

orders.ordered_atの型を選ぶには、注文日時として何を記録するのかを決めます。受信した表記をそのまま残すのか、特定の地域の日付と時刻を記録するのか、注文が起きた一瞬を記録するのかで選択が変わります。ここでは文字列型、timestamp without time zone、timestamp with time zoneを比較します。

3.1. 受信した表記を残すなら文字列型を選べる

受信した表記をそのまま残す必要があるなら、文字列型を選べます。UTCとの時差を表すオフセットやタイムゾーン名、部分的な日時、仕様外の値も、監査記録として保存できます。PostgreSQLや、JDBCやODBCなどのデータベースドライバに日時として解釈・変換させず、アプリケーション側だけで解釈する方針にも合います。

ただし、文字列型では日時としての妥当性は保証されません。データベースで日時の範囲検索や演算をするなら、日付時刻型を使います。検索と受信原文の保存の両方が必要なら、検索用の日付時刻型列と原文用の文字列列を分けます。

文字列の並び順を日時順として使うには、入力形式と照合順序（文字列比較の規則）を統一します。たとえば日付をYYYY-MM-DDにそろえ、桁数、精度、表記規則を固定すれば、文字列の順序を日付順として利用できます。ただし、異なるタイムゾーンの日時を同じ列に保存すると、表記上の順序と実際の時系列が一致しない場合があります。形式と照合順序を統一するだけで目的を満たせるか確認が必要です。

3.2. timestamp without time zoneは時差を変換しない

orders.ordered_atに指定したtimestamp without time zoneは、日付と時刻を保存しますが、タイムゾーン情報は持ちません。タイムゾーン付きの文字列を入力しても、時差の部分は無視されます。次の例では、同じ入力をtimestamp with time zoneにも保存し、セッションのタイムゾーンを変えて表示を比較します。

SET TIME ZONE 'Asia/Tokyo';

CREATE TEMP TABLE orders_time (
  ts_without timestamp without time zone,
  ts_with timestamp with time zone
);

INSERT INTO orders_time VALUES
('2026-07-01 09:00:00+09', '2026-07-01 09:00:00+09');

SELECT ts_without, ts_with FROM orders_time;
     ts_without      |        ts_with
---------------------+------------------------
 2026-07-01 09:00:00 | 2026-07-01 09:00:00+09
(1 row)

SET TIME ZONE 'UTC';

SELECT ts_without, ts_with FROM orders_time;
     ts_without      |        ts_with
---------------------+------------------------
 2026-07-01 09:00:00 | 2026-07-01 00:00:00+00
(1 row)

ts_withは、Asia/Tokyoでは09:00:00+09、UTCでは00:00:00+00と表示されます。一方、ts_withoutはどちらでも09:00:00です。timestamp without time zoneでは、入力に含まれる+09を使った変換も、表示先のタイムゾーンに合わせた変換も行われません。

timestamp without time zoneが適するのは、特定の地域の日付と時刻そのものを扱う場合です。たとえば、店舗の営業開始日時やシフト開始日時なら、timestamp without time zoneを検討できます。その場合は、どの地域の日時かを店舗や拠点の情報と一緒に管理します。注文が実際に起きた時点を複数の地域から比較する用途には、次のtimestamp with time zoneが適しています。

3.3. timestamp with time zoneは同じ一瞬を保存する

timestamp with time zoneは、タイムゾーンを考慮して日時を扱います。明示的なタイムゾーンを含む入力はUTCに変換して保存し、出力時にはセッションのTimeZoneに合わせて表示します。入力時のタイムゾーン名や+09という指定自体は保存しません。

注文受付時刻、決済完了時刻、ログ発生時刻のように、同じ一瞬を複数の地域から比較するなら、timestamp with time zoneが適しています。orders.ordered_atをこの用途に使うなら、timestamp without time zoneから変更する候補になります。前節の例で表示が変わったのは、保存した一瞬はそのままに、表示先のタイムゾーンに合わせて変換したためです。

ただし、タイムゾーンのない入力をtimestamp with time zoneに渡すと、セッションのTimeZoneで解釈されます。アプリケーションの接続設定、データベースドライバによる日付時刻型への変換、取得後の表示設定が一致しないと、意図しない時刻として扱われる可能性があります。入力にタイムゾーンを含めるか、セッションのTimeZoneを固定するかを決め、取得後の表示先も明確にします。

保存したい値ごとの選択肢をまとめます。日付時刻型の仕様は、公式文書の「日付/時刻データ型」と「日付/時刻の入力」を参照してください。

4. JSON型は原文の保持と検索の必要性で選ぶ

4.1. text、json、jsonbは保存後の用途が異なる

orders.payment_payloadには、決済サービスから返されたJSON形式のデータを保存します。保存先にはtext、json、jsonbがあり、受信内容をどこまでそのまま残すか、保存後にどう検索するかで選びます。

textはJSONとして検証しないため、JSONとして不正な文字列も保存できます。NUL文字だけは保存できません。これは、PostgreSQLのtext型自体がNUL文字を保存できないためです。実装上のサイズ上限は約1GBです。JSONとしての妥当性を検証せず、受信内容を残すことが目的なら候補です。

jsonは入力がJSONとして正しいことを検証し、入力されたテキストを保存します。空白、キーの順序、重複キーも保持します。冒頭の定義のようにpayment_payloadをjsonにすると、JSONとして検証しながら受信時の表記を残せます。

jsonbは入力を解析し、分解した形式で保存します。そのため、保存後の検索や比較に向いています。入力時に変換が必要ですが、GINインデックスも利用できます。一方、空白やキーの順序は保持されず、重複キーは最後の値だけが残ります。次のSQLで、重複キーの扱いを比較できます。

SELECT '{"a": 1, "a": 2, "active": false}'::json;
               json
-----------------------------------
 {"a": 1, "a": 2, "active": false}
(1 row)

SELECT '{"a": 1, "a": 2, "active": false}'::jsonb;
            jsonb
---------------------------
 {"a": 2, "active": false}
(1 row)

jsonでは2つのaが入力どおりに残りますが、jsonbでは最後の値である2だけが残ります。受信時の表記を保持するならjson、JSONとしての検証もせず文字列を残すならtext、JSONの内容を検索・比較するならjsonbを選びます。

4.2. JSONのキーや値を検索するならjsonbを使う

決済レスポンスのトップレベルにerror_codeキーがある注文を調べるなら、orders.payment_payloadをjsonbにする方法があります。冒頭のテーブル定義どおり列がjson型の場合、次の検索を実行するとエラーが発生します。?演算子はjsonb型に対して定義されているためです。

SELECT id
FROM orders
WHERE payment_payload ? 'error_code';
ERROR:  operator does not exist: json ? unknown
LINE 3: WHERE payment_payload ? 'error_code';
                              ^
HINT:  No operator matches the given name and argument types. You might need to add explicit type casts.

列をjsonbに変更すると、同じ条件でキーの有無を調べられます。

ALTER TABLE orders
  ALTER COLUMN payment_payload TYPE jsonb
  USING payment_payload::jsonb;

SELECT id
FROM orders
WHERE payment_payload ? 'error_code';
 id
----
(0 rows)

jsonやtextで保存し、検索のたびにjsonbへ変換する方法もあります。ただし、変換が必要になり、インデックスも検索式に合わせて定義しなければなりません。

検索量が増えたら、jsonb列へのGINインデックスも検討します。GINは、JSONのキーや値などを使った検索に利用できるインデックスです。標準のGIN演算子クラスでは、キーの存在、包含、JSONPathによる検索などを扱えます。詳しくは公式文書の「JSONデータ型」と「jsonbインデックス」を参照してください。

5. まとめ: 列の用途から型を決める

ordersの各列を見直すと、値の形式だけでは型を決められないことが分かります。注文コードには長さの上限、金額には計算の正確さ、注文日時にはタイムゾーンの扱い、決済レスポンスには原文の保持と検索方法を考える必要があります。

文字列では、最大長を型に記すならvarchar(n)、業務条件を制約で管理するならtextとCHECK制約を検討します。char(n)は空白埋めと比較規則に注意が必要で、性能上の利点もないため、新規設計で選ぶ場面は限られます。また、長い文字列を保存できても、値全体をB-treeインデックスのキーにできるとは限りません。

数値では、10進数の正確さが必要ならnumeric、整数で足りるなら値の範囲に応じてintegerかbigintを選びます。double precisionは近似値を許容できる用途に使い、金額では丸める時点と規則も決めます。moneyは設定や演算規則に依存するため、新規設計で使う場合は注意が必要です。

日付時刻では、同じ一瞬を扱うならtimestamp with time zone、特定の地域の日付と時刻を扱うならtimestamp without time zoneを検討します。受信した表記を残すために文字列型を選ぶこともできますが、その場合は日時としての妥当性や比較方法を別途管理します。

JSONでは、受信文字列を検証せずに残すならtext、JSONとして検証して入力テキストを保持するならjson、内容を検索・比較するならjsonbが候補です。決済レスポンスを記録として残すのか、内容を使って注文を検索するのかで、適する型が変わります。

テーブルを設計するときは、列ごとに保存する値と用途を確かめます。拒否する入力、必要な計算、検索条件が明確になれば、型を選ぶ理由も定まります。