■ はじめに

PostgreSQLのCOPY FROMでエラー「missing data for column xxxx」
になったのでメモ。

【１】現象

PostgreSQL で 以下の※１のようにCOPY FROMを使った際に
以下の「【２】エラーメッセージ」が表示する

なお、PostgreSQLの環境は、以下の関連記事を参照のこと

Docker compose ～ Pgweb/pgAdmin ～
https://dk521123.hatenablog.com/entry/2023/08/10/111919

※１：SQL

CREATE SCHEMA demo;

CREATE TABLE IF NOT EXISTS demo.products (
    "id" INTEGER NOT NULL,
    "product_name" VARCHAR(50) NOT NULL,
    "price" INTEGER NOT NULL,
    "description" VARCHAR(200) NULL DEFAULT NULL,
    "created_at" TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY ("id")
);

COPY demo.products FROM '/docker-entrypoint-initdb.d/input.csv' 
WITH CSV HEADER QUOTE '"';

input.csv

id,product_name,price,description,created_at
"101","prod - A","1200","This is a test.","2020-05-08 11:22:22.222"
"102","prod - B","230","Hello, world!","2020-05-08 11:22:22.222"
"103","prod - C","100","This is a test.","2020-05-08 11:22:22.222"
"104","prod - D","1560","","2020-05-08 11:22:22.222"
"105","prod - E","1210","This is a test.","2020-05-08 11:22:22.222"
"106","prod - F","2930",,"2020-05-08 11:22:22.222"
"107","prod - F","1222",

【２】エラーメッセージ

ERROR:  missing data for column "created_at"
CONTEXT:  COPY products, line 8: ""107","prod - F","1222"," 

SQL state: 22P04

【３】原因

* CSVの一部（id=107）がフォーマットが崩れていたため。

【４】解決案

案１：CSVを作り直す

"107","prod - F","2930",,"2020-05-08 11:22:22.222"

補足：ON_ERROR ignore

PostgreSQL 17.0以上だと「ON_ERROR ignore」が使えるって話だったが
変わらなかった。

サンプル

-- Version確認
select * From version();
-- "PostgreSQL 17.0 (Debian 17.0-1.pgdg120+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 12.2.0-14) 12.2.0, 64-bit"


-- 変わらずエラー
COPY demo.products FROM '/docker-entrypoint-initdb.d/input.csv' 
WITH (FORMAT CSV, HEADER, QUOTE '"', ON_ERROR ignore, LOG_VERBOSITY verbose);