PostgreSQLへのCSVインポートで特定の文字列をNULL扱いにする方法

  • PostgreSQL
  • AWS
  • SQL

この記事は、構成と文章の整理にAIを使っています。
あくまで読みやすくするための補助で、内容そのものをAIにすべて任せて書いているわけではありませんが、ところどころ相談しながら整えています。

CSVをPostgreSQLにインポートするとき、CSV側の特定の文字列（空文字や\N以外の何か）をNULLとして扱いたいことがある。方法はいくつかあって、書き方がそれぞれ微妙に違うのでメモ。

COPYコマンドでの指定

サーバー側でファイルを読ませるCOPYはこう。

COPY tablename (column1, column2, column3)
FROM '/path/to/file.csv'
DELIMITER ','
CSV HEADER
NULL AS '***';

公式ドキュメントの構文定義はNULL [ AS ] 'null string'になっていて、ASは省略可能。付けても付けなくても意味は同じ。デフォルトはCSVモードだと無引用の空文字列。

\copyのWITH句だと書き方が変わる

クライアント側から読み込む\copyを、新しいWITH (...)形式でオプションをまとめて書く場合はASを付けない。

\copy tablename (column1, column2, column3)
FROM '/path/to/file.csv'
WITH (FORMAT csv, DELIMITER ',', HEADER true, NULL '***')

NULL AS '***'ではなくNULL '***'。旧形式（ASありなし両対応）と新形式（ASなし）が混在してるので、どっちの構文を使っているか意識しておく必要がある。

aws_s3.table_import_from_s3でも同じ考え方

RDS/AuroraのPostgreSQLでS3から直接インポートするaws_s3.table_import_from_s3は、第3引数のoptionsにCOPYコマンドの引数をそのまま文字列で渡す仕組みになっている。

IAMロールでS3にアクセスする場合の例（bucket/file_path/regionを直接渡す形）。

SELECT aws_s3.table_import_from_s3(
    'tablename',
    'column1, column2, column3',
    '(format csv, header true, delimiter '','', quote ''"'', escape ''"'', null ''***'')',
    's3-bucket-name',
    's3-key',
    'ap-northeast-1'
);

引数の並びはtable_name, column_list, options, bucket, file_path, region。s3_info（aws_commons.create_s3_uri）を使う4引数版のシグネチャもあるけど、こっちのほうが素直で読みやすい。IAMロール方式なのでcredentials引数は不要。