PostgreSQLのCSVインポート：デフォルト値、IDENTITY、生成列

「データベース側で値を決定する」とは、インポートの方針であり、PostgreSQLの特定の列型ではありません。DEFAULT now()を持つタイムスタンプは、入力データから指定してよい過去のイベント時刻を表すことも、入力データから決して変更してはいけない監査時刻を表すこともあります。DDLだけでは、その業務上の意味を決められません。

フィールドごとに判断を記録します。過去のタイムスタンプについては、入力元のタイムゾーンと値が欠落した場合の扱いも記録してください。識別子は、外部参照なのか本番環境の主キーなのかを明示します。名前が一致しても、同じ対象を識別しているとは限りません。

使い捨てのPostgreSQL 17データベースに接続した、1つのpsqlセッションでこの例を実行します。一時テーブルは、セッションの終了時に消えます。

CREATE TEMP TABLE import_lines_demo (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  external_ref text NOT NULL UNIQUE,
  quantity integer NOT NULL CHECK (quantity > 0),
  unit_price numeric(12,2) NOT NULL CHECK (unit_price >= 0),
  status text NOT NULL DEFAULT 'new',
  total numeric GENERATED ALWAYS AS (quantity * unit_price) STORED
);

COPY import_lines_demo (external_ref, quantity, unit_price)
FROM STDIN WITH (FORMAT csv, HEADER MATCH);
external_ref,quantity,unit_price
line-a,2,12.50
line-b,3,4.00
\.

SELECT id, external_ref, status, total
FROM import_lines_demo ORDER BY id;

結果にはID 1と2が含まれ、両方の行のステータスがnew、合計がそれぞれ25.00と12.00になります。これはpsqlのインライン入力の例で、バックスラッシュとドットを記述した行がデータストリームを終了します。WebのSQLエディターに貼り付けるためのブロックではありません。

入力フィールドが3つなので、インポートする列リストも3列です。ID、ステータス、合計は入力に含まれていません。PostgreSQLが、それぞれの異なる定義に従って値を設定します。6フィールドを送って空欄が都合よく解釈されることを期待するより、レビューしやすい方法です。

別のローカルファイルを使う場合も、psqlの\copyで同じ列リストを使用します。フィールドの順序、省略された列、入力オプションの解釈は、COPYのリファレンスに規定されています。

PostgreSQLのデフォルトのCSV設定では、引用符で囲まれていない空のフィールドはNULLを表します。引用符で囲まれた空のフィールド""は、空文字列を表します。どちらも、入力列リストから列を省略することとは異なります。

external_ref,quantity,unit_price,status
line-c,1,8.00,
line-d,1,8.00,""

ステータスを列リストに含め、デフォルトのCSVオプションでインポートすると、最初のレコードはテーブルのNOT NULL制約に違反します。2つ目はnewではなく、空のステータスを指定することになります。このデモ用テーブルには、許可されたステータスを検査するCHECKがないため、空文字列だけであれば受け入れられます。これは、「PostgreSQLが受け付けた」値でも、アプリケーションにとっては誤っている場合があることを示しています。

INSERT文では、SQLのDEFAULTでデフォルト値を明示的に要求できます。通常のCSVフィールドに書いたDEFAULTは、単なるテキストです。PostgreSQL 17には、COPY用のDEFAULTマーカーオプションが別にあります。合意済みで値が衝突しないファイル規約がある場合にのみ使ってください。列全体を省略できるなら、このオプションは不要です。

一部のレコードでは意図的に値を指定し、ほかのレコードではデフォルト値が必要な場合、単一の省略列リストでは両方を表現できません。文書化したデフォルトマーカー、書き込み対象の列構成による行のグループ分け、ステージング後に意図を明示したINSERT文を実行する方法などが考えられます。データの管理責任者が実際に承認していない限り、この区別を「空欄はデフォルト値を意味する」という規則に置き換えてはいけません。

GENERATED ALWAYS AS IDENTITYは、通常のINSERTで不用意にIDENTITYの値を指定することを防ぎます。それでもCOPY FROMは、入力に含まれるIDを使用できます。これは、認可の境界ではなく、インポートで明示的に利用できる機能として扱ってください。

先ほどのデモに続けて、次のCOPYで指定したIDを挿入できます。

COPY import_lines_demo (id, external_ref, quantity, unit_price)
FROM STDIN WITH (FORMAT csv);
900,line-explicit,1,1.00
\.

取り込み先で新しいIDを生成する方針なら、マッピングとCOPYの列リストの両方からidを除外します。IDを意図的に保持する場合は、競合と関連するシーケンスの状態を確認してください。IDENTITY値を指定しただけでは、シーケンスはインポートした最大のIDまで進みません。後で生成される値が、いずれ衝突する可能性があります。

シーケンスの修正は、データベース所有者の移行計画で扱います。アプリケーションが並行して書き込んでいる最中に、汎用的な「シーケンスを最大IDに設定する」コマンドを実行してはいけません。すでに値が採番されている可能性があります。選んだ手順を、アプリケーションの並行実行とメンテナンスの条件に合わせて事前検証してください。仕組みは、IDENTITYのドキュメントとシーケンス関数のリファレンスで説明されています。

顧客の表計算ファイルには、独自の「Total」列があるかもしれません。例のテーブルでは、取り込み先の合計は数量と単価から計算します。入力元の合計は書き込みマッピングから外し、有用な証拠になる場合は、別途比較してください。

合計が一致しなければ、どちらの規則が誤っているか判断します。入力元のシステムは丸める前に税額を計算し、取り込み先は先に丸めているかもしれません。取り込み先の生成値を上書きするのは、修正方法にはなりません。承認済みの入力変換か、レビュー済みの取り込み先モデルの変更が必要です。

PostgreSQL 17は格納型の生成列に対応しています。生成処理は、基になる行への関係するBEFOREトリガーの変更後に行われます。そのため、生成式があるだけでは、プレビューで先に計算した値と同じになるとは限りません。式と適用されるトリガーを読み、実際の書き込み経路をテストしてください。PostgreSQL 17の生成列の規則を参照してください。

各列について、値を設定する主体、省略時の規則、入力データによる上書きの許可、その判断の根拠を記録します。正確な取り込み先テーブルと、順序付きのCOPY列リストも含めてください。必須値をアプリケーションが設定する場合は、その値の根拠となる権限主体を明示します。

• 意味のある箇所では、省略、NULL、空文字列をそれぞれ別にテストする。
• 保護対象のすべての列で、入力データから上書きを試みるテストを行う。
• IDを保持する場合、シーケンスの調整や参照マッピングの手順が必要か確認する。
• 挿入後に、格納された派生値を承認済みの業務ルールと比較する。

PG Import Checkは、指定したDDLの分類に役立ちます。入力データで必須の値、通常のデフォルト値、IDENTITY、生成式を区別して扱います。構造上の検出事項と、解析でカバーできない点も示します。式の実行、実際のスキーマの調査、入力レコードの検証、デフォルト値を根拠とするフィールドへの値指定の許可の推定は行いません。

テナントIDなどのフィールドについては、権限とRLSのガイドに進んでください。結果の値を確認するには、インポート後の検証を参照してください。