Postgresql環境でCSVからintegerにNULL(空データ)をcopyする方法について

Question

前提・実現したいこと

Postgresql環境でCSVからcopyを試みましたが、Integer型のカラムに対して空のデータ（QUOTEの""のみ）があった為syntax errorが発生しました。NULLとして入れることで対処しようと思いましたがうまく行かず、ダミーのテーブルとCSVを作って試したが同様に失敗した。

同様の事象と思われる質問もありましたが、別の方法で対処されており参考のURLを見ても解決出来ませんでした。何か心当たりなどあるかたがおられましたらご教授いただけたらと思います。

該当のソースコード

copy public.test (inta, strb) FROM '//xxxxxxx/test_dummy.CSV' with CSV null as '' HEADER ENCODING 'SHIFT_JIS_2004' QUOTE '"' ESCAPE '''';

発生している問題・エラーメッセージ

ERROR:  "integer"型の入力構文が不正です: ""
CONTEXT:  testのCOPY、行 5、列 inta: ""

テスト用に作成したテーブル

CREATE TABLE public.test(
    inta integer,
    strb character varying(1000) COLLATE pg_catalog."default"
)

copyに使用したCSVの中身

"数値","文字"
"1","A"
"2","B"
"3",""
"","D"
"5","E"

試したこと

・with null as '' で空文字("")をnullとして認識させようとしたが変化無し
・INSERT INTO test VALUES (NULL, 'TEST');　は問題なくinsert出来た

補足情報（FW/ツールのバージョンなど）

利用環境：
Windows 10
PostgreSQL 13.1, compiled by Visual C++ build 1914, 64-bit
pgAdmin 4.28

Answer 1

〈with null as '' で空文字("")をnullとして認識させようとした〉
っていう判断の根拠になるものって、なんでしょう？

COPY
を見るに、

NULL 'null_string'

って指定を使うべきでしょうね。
CSV（やCSV出力元）でなにかルールを決めて文字列を詰めておかないといけないかと。

CSV形式にはNULL値と空文字列とを区別する標準的な方法はありません。 PostgreSQLのCOPYでは引用符によってこれを区別しています。 NULLはNULLパラメータの文字列として出力され、引用符で囲まれません。 一方、NULLパラメータの文字列に一致する非NULL値は引用符で囲まれます。 たとえばデフォルトの設定では、NULLは引用符付けのない空文字列として出力され、空文字列のデータ値は2つの引用符（""）で出力されます。 データの読み込みの際も同様の規則に従います。 FORCE_NOT_NULLを使用して、特定列に対しNULL入力の比較を行わないようにすることもできます。 またFORCE_NULLを使うことで、引用符付きのNULL文字列のデータの値をNULLに変換することもできます。

Comments

[okkoto]

根拠、と呼べるほどのものはありませんでした。他の方の事例から、となりますがCSVの場合デフォルトが空文字なのであまり意味を無いのかもしれません。今回の場合はCSVに合わせてDBを用意していたので代替もできず苦戦しておりました。

結果として引用していただいた文のForce_nullにより解決することが出来ました。ありがとうございました。

Answer 2

with null as '' で空文字("")をnullとして認識させようとしたが変化無し

Null '""'
ではどうですか。
上記は駄目ですね。

文字型のダミーの一時テーブルにインポートして、そこから正式なテーブルにinsertするようにしてはどうですか。

追記

FORCE_NULL指定で大丈夫そうですね。

copy public.test(inta, strb) FROM '//xxxxxxx/test_dummy.CSV'
with(format CSV, FORCE_NULL(inta), HEADER true, ENCODING 'SHIFT_JIS_2004', ESCAPE '''')
;

FORCE_NULL
指定された列の値を、それが引用符付きであったとしても、NULL文字列と比較し、一致した場合は値をNULLにセットします。 NULL文字列が空であるデフォルトでは、引用符付きの空文字列をNULLに変換します。 このオプションはCOPY FROMで、かつCSV形式を使用する場合のみ許されます。

FORCE_NULLを使うことで、引用符付きのNULL文字列のデータの値をNULLに変換することもできます。

Comments

[okkoto]

ありがとうございました。
FORCE_NULL　で意図した通りにintegerのカラムにNULLが入るようになりました。