PostgreSQL CSVインポート｜COPYと\copyの違いとエラー対処

PostgreSQLでCSVを取り込む・書き出すには、SQLの COPY か、psqlのメタコマンド \copy を使います。2つの違いは「CSVファイルをどこで読むか」で、COPY はデータベースサーバーのファイルを、\copy は手元（psqlを動かしているPC）のファイルを扱います。 迷ったら、まず \copy を選ぶと権限エラーに出会いにくくなります。

この記事では、PostgreSQL 16.15で実際に実行したコマンドと結果をそのまま載せています。COPYと\copyの判断表、オプション一覧（PostgreSQL 16の DEFAULT、17の ON_ERROR を含む）、よく出るエラー8種の再現と直し方、さらにPythonとNode.jsから10万行を取り込んだときの INSERTとCOPYの速度差の実測値 までまとめました。

PostgreSQL CSVインポート・エクスポートの早見表

COPYと\copyの違いと選び方

COPY ... FROM 'ファイル名' は、PostgreSQLのサーバープロセスがサーバー側のディスクからファイルを読みます。\copy は、psqlが手元のファイルを読み、中身を COPY ... FROM STDIN としてサーバーへ送ります。 書き出し（TO）も同じ関係です。

判断表：どちらを使うか

選び方の目安はシンプルです。 手元のCSVを扱うなら \copy、DBサーバーにログインしてサーバー上のファイルを扱うなら COPY、アプリから扱うなら COPY ... FROM STDIN をドライバ経由で使います（後半のPython・Node.jsの例）。

権限の違いを実行して確認する

一般ユーザー t107_app（テーブルへの SELECT, INSERT, UPDATE, DELETE のみ付与）で試した結果です。

-- 準備（スーパーユーザーで実行）
CREATE TABLE items(
  id integer PRIMARY KEY,
  name text NOT NULL,
  price integer NOT NULL,
  stock integer,
  note text,
  created_at timestamptz DEFAULT now()
);
CREATE ROLE t107_app LOGIN PASSWORD 't107pw';
GRANT SELECT, INSERT, UPDATE, DELETE ON items TO t107_app;

取り込むCSV（items.csv）：

id,name,price,stock,note
1,ボールペン,120,50,黒
2,ノート,180,30,"A5,方眼"
3,クリップ,90,,
4,"付箋 ""強粘着""",250,12,

一般ユーザーで COPY を実行すると、次のエラーになります。

t107=> COPY items(id,name,price,stock,note) FROM '/tmp/items.csv' WITH (FORMAT csv, HEADER true);
ERROR:  permission denied to COPY from a file
DETAIL:  Only roles with privileges of the "pg_read_server_files" role may COPY from a file.
HINT:  Anyone can COPY to stdout or from stdin. psql's \copy command also works for anyone.

同じユーザーでも \copy なら成功します。

t107=> \copy items(id,name,price,stock,note) FROM 'items.csv' WITH (FORMAT csv, HEADER true)
COPY 4
t107=> SELECT id, name, price, stock, note, note IS NULL AS note_null FROM items;
 id |     name      | price | stock |  note   | note_null
----+---------------+-------+-------+---------+-----------
  1 | ボールペン    |   120 |    50 | 黒      | f
  2 | ノート        |   180 |    30 | A5,方眼 | f
  3 | クリップ      |    90 |       |         | t
  4 | 付箋 "強粘着" |   250 |    12 |         | t
(4 rows)

カンマを含む値（"A5,方眼"）や、"" でエスケープした引用符も正しく取り込めています。CSV形式では、引用符で囲まない空欄はNULLになります。

サーバー側の COPY を一般ユーザーに許可するときは、目的のロールだけを付与します。

GRANT pg_read_server_files TO t107_app;   -- COPY FROM 'file' を許可
GRANT pg_write_server_files TO t107_app;  -- COPY TO 'file' を許可
-- COPY ... PROGRAM（外部コマンド実行）は pg_execute_server_program

これらのロールはサーバー上の任意のファイルを読み書きできる強い権限なので、運用では \copy（または FROM STDIN）で足りるかを先に検討すると安心です。 ロール設計の詳細はロールと権限の記事に譲ります。

CSVを取り込む（インポート）の書き方

ヘッダー付きCSVを取り込む

\copy items(id,name,price,stock,note) FROM 'items.csv' WITH (FORMAT csv, HEADER true)

• FORMAT csv を付けないと、既定の text 形式（タブ区切り）として読まれます。 CSVなら必ず付けます。
• 列リストを書くと、CSVの列順とテーブルの列順が違っても対応づけられます。 書かない列（上の例では created_at）には列の既定値が入ります。
• \copy は1行で書く必要があります（途中で改行できません）。セミコロンも付けません。

ヘッダーの列名までチェックする（HEADER MATCH）

PostgreSQL 15以降は HEADER match で、ヘッダーの列名と数が列リストと一致するかを検査できます。列のずれによる取り違えを防げます。

t107=> \copy items(id,name,price,stock,note) FROM 'fewcols.csv' WITH (FORMAT csv, HEADER match)
ERROR:  wrong number of fields in header line: got 3, expected 5
CONTEXT:  COPY items, line 1: "id,name,price"

条件に合う行だけ取り込む（WHERE）

PostgreSQL 12以降、COPY FROM に WHERE を付けられます。WITH (...) の中ではなく、後ろに書きます。

t107=> \copy items(id,name,price,stock,note) FROM 'items.csv' WITH (FORMAT csv, HEADER true) WHERE price >= 150
COPY 2

WITH (..., WHERE price >= 150) と中に書くと ERROR: syntax error at or near ">=" になりました。

既定値を使いたい欄を指定する（DEFAULT、PostgreSQL 16以降）

PostgreSQL 16で追加された DEFAULT オプションは、指定した文字列が来た欄に、列の既定値を入れます。 NULLとは別に「既定値を使う」を表現できます。

-- def.csv
id,name,price,stock,note,created_at
21,クリアファイル,40,200,,\D

t107=> \copy items(id,name,price,stock,note,created_at) FROM 'def.csv' WITH (FORMAT csv, HEADER true, DEFAULT '\D')
COPY 1
t107=> SELECT id, name, created_at IS NOT NULL AS has_created FROM items WHERE id = 21;
 id |      name      | has_created
----+----------------+-------------
 21 | クリアファイル | t
(1 row)

created_at に now() の値が入りました。

NULLと空文字を区別して取り込む

CSV形式では「引用符なしの空欄＝NULL」「""＝空文字」が既定です。NULL オプションや FORCE_NULL / FORCE_NOT_NULL で変えられます。

fn.csv・fnn.csv は、どちらも note 列が1行目 ""、2行目 空欄 のCSVです。

t107=> \copy items(id,name,price,stock,note) FROM 'fn.csv'  WITH (FORMAT csv, HEADER true, FORCE_NULL (note))
COPY 2
t107=> \copy items(id,name,price,stock,note) FROM 'fnn.csv' WITH (FORMAT csv, HEADER true, FORCE_NOT_NULL (note))
COPY 2
t107=> SELECT id, note, note IS NULL AS note_null FROM items WHERE id BETWEEN 24 AND 27 ORDER BY id;
 id | note | note_null
----+------+-----------
 24 |      | t
 25 |      | t
 26 |      | f
 27 |      | f
(4 rows)

NULL 'NULL' を指定すると文字列 NULL をNULLとして読みます。このとき引用符なしの空欄はNULLではなく空文字になる点に注意します（実行して確認済み）。

CSVを書き出す（エクスポート）の書き方

テーブル全体・クエリ結果を書き出す

t107=> \copy (SELECT id,name,price FROM items WHERE price >= 150 ORDER BY id) TO 'expensive.csv' WITH (FORMAT csv, HEADER true)
COPY 2

-- expensive.csv
id,name,price
2,ノート,180
4,"付箋 ""強粘着""",250

クエリ結果を書き出すときは、SELECT文を丸かっこで囲みます。 JOINや集計の結果もそのままCSVにできます。

全項目を引用符で囲む・区切り文字を変える

t107=> \copy items(id,name,price,stock,note) TO 'all.csv' WITH (FORMAT csv, HEADER true, FORCE_QUOTE *)
COPY 4

id,name,price,stock,note
"1","ボールペン","120","50","黒"
"2","ノート","180","30","A5,方眼"
"3","クリップ","90",,
"4","付箋 ""強粘着""","250","12",

NULLは FORCE_QUOTE でも引用符で囲まれず、空欄のまま出力されます。 NULLを目に見える文字にしたいときは NULL '(null)'、区切り文字を変えるときは DELIMITER ';' を加えます。

Shift_JISで書き出す

\copy (SELECT id,name FROM items) TO 'out_sjis.csv' WITH (FORMAT csv, HEADER true, ENCODING 'SJIS') のように ENCODING 'SJIS' を付けると、Shift_JISで出力されます（実行し、iconvで読み戻して確認済み）。Excelで開くときの文字化けの考え方は、CSVの文字化けの記事で詳しく扱っています。

COPYオプション一覧

WITH ( ... ) の中に、カンマ区切りで書きます。

ON_ERROR と LOG_VERBOSITY はPostgreSQL 17の公式ドキュメントで確認した内容です。PostgreSQL 16で ON_ERROR を書くと、次のエラーになります（実行結果）。

ERROR:  option "on_error" not recognized
LINE 1: ...,note ) FROM STDIN WITH (FORMAT csv, HEADER true, ON_ERROR i...

PostgreSQL 17以降では、ON_ERROR ignore を付けると型変換できない行を飛ばし、最後に飛ばした件数がNOTICEで表示されます。対象は データ型への変換エラー で、列数の過不足や制約違反（主キー重複など）は対象外です（公式ドキュメントの記述に基づく。PostgreSQL 17では未実行）。

よくあるエラーと直し方（PostgreSQL 16で再現）

No such file or directory と Permission denied

t107=> COPY items FROM '/tmp/t107_nofile.csv' WITH (FORMAT csv, HEADER true);
ERROR:  could not open file "/tmp/t107_nofile.csv" for reading: No such file or directory
HINT:  COPY FROM instructs the PostgreSQL server process to read a file. You may want a client-side facility such as psql's \copy.

t107=> COPY items(id,name,price,stock,note) FROM '/home/me/work/items.csv' WITH (FORMAT csv, HEADER true);
ERROR:  could not open file "/home/me/work/items.csv" for reading: Permission denied
HINT:  （上と同じ）

（2つ目は実際には作業ディレクトリのパスで再現し、表示ではパスを短くしています。）

どちらもHINTのとおり、ファイルを読んでいるのは「サーバープロセス（postgresユーザー）」です。 手元のファイルなら \copy に変えれば解決します。サーバー上のファイルを使うなら、postgresユーザーが親ディレクトリを辿れて、ファイルを読める（例：chmod 644）場所に置きます。

\copy でファイルがないときは、エラーの形が変わります（psqlが出すため）。

t107=> \copy items FROM 'nofile.csv' WITH (FORMAT csv)
nofile.csv: No such file or directory

extra data after last expected column

値の中にカンマがあるのに引用符で囲まれていないと、列が1つ増えたと解釈されます。

-- extra.csv（直す前）
id,name,price,stock,note
10,定規,150,5,30cm,透明

ERROR:  extra data after last expected column
CONTEXT:  COPY items, line 2: "10,定規,150,5,30cm,透明"

直し方：値を引用符で囲みます（10,定規,150,5,"30cm,透明"）。CSVを作る側で、カンマ・改行・引用符を含む値を囲む設定にするのが確実です。CONTEXT に行番号が出るので、大きなファイルでも場所をすぐ特定できます。

invalid input syntax for type integer

ERROR:  invalid input syntax for type integer: "三百"
CONTEXT:  COPY items, line 2, column price: "三百"

行番号と列名まで表示されます。 値の揺れが多いCSVは、いったん全列 text の作業用テーブルに取り込み、WHERE price !~ '^[0-9]+$' のようにSQLで不正な行を確認してから、price::int で変換して本テーブルへ入れると扱いやすくなります（一時テーブルの作り方は下の主キー重複の例と同じです）。

Shift_JISのCSV：invalid byte sequence for encoding "UTF8"

t107=> \copy items(id,name,price,stock,note) FROM 'sjis.csv' WITH (FORMAT csv, HEADER true)
ERROR:  invalid byte sequence for encoding "UTF8": 0x95
CONTEXT:  COPY items, line 2
t107=> \copy items(id,name,price,stock,note) FROM 'sjis.csv' WITH (FORMAT csv, HEADER true, ENCODING 'SJIS')
COPY 1

ENCODING 'SJIS' を付けるだけで取り込めます。 PostgreSQLの SJIS はWindowsのCP932相当（WIN932 は別名）で、①や髙を含むCP932のCSVも ENCODING 'SJIS' で取り込めました（実行して確認）。SET client_encoding TO 'SJIS'; でも同じ効果ですが、セッション全体に影響するので、ENCODING オプションのほうが範囲を限定できます。

BOM付きUTF-8のCSV

ExcelでUTF-8 CSVとして保存すると、先頭にBOM（3バイトの目印 EF BB BF）が付きます。

ERROR:  invalid input syntax for type integer: "20"
CONTEXT:  COPY items, line 1, column id: "20"

表示上は "20" に見えますが、先頭に見えないBOMが付いています。 取り込む前にBOMを取り除きます。

# 1行目先頭のBOMだけを削除（GNU sed）
sed -i '1s/^\xEF\xBB\xBF//' bom_nohead.csv

t107=> \copy items(id,name,price,stock,note) FROM 'bom_nohead.csv' WITH (FORMAT csv)
COPY 1

duplicate key value violates unique constraint

ERROR:  duplicate key value violates unique constraint "items_pkey"
DETAIL:  Key (id)=(1) already exists.
CONTEXT:  COPY items, line 2

COPYは1件でもエラーになると全体が取り消され、1行も入りません。 再取り込みなら先に対象を削除するか、一時テーブルに取り込んでから INSERT ... ON CONFLICT で入れます（UPSERTの詳細は別記事で扱っています）。

CREATE TEMP TABLE items_tmp (LIKE items INCLUDING DEFAULTS);
\copy items_tmp(id,name,price,stock,note) FROM 'items.csv' WITH (FORMAT csv, HEADER true)
INSERT INTO items SELECT * FROM items_tmp
ON CONFLICT (id) DO UPDATE SET name = EXCLUDED.name, price = EXCLUDED.price;

なお、serial や IDENTITY の列にCSVで番号を直接入れたあとは、シーケンスを setval で合わせておくと、次のINSERTで番号が重なりません。

PythonとNode.jsからCOPYで取り込む

アプリから大量のCSVを取り込むときも、ドライバのCOPY機能（COPY ... FROM STDIN）を使えます。ファイルはアプリ側で読むので、サーバー側の権限は不要です。

検証環境：PostgreSQL 16.15、Python 3.14.7 + psycopg 3.3.6、Node.js 22.22.2 + pg 8.23.1 + pg-copy-streams 7.0.0。同じマシン上で接続（127.0.0.1）。

テーブルとデータ（10万行、約3.9MB）：

CREATE TABLE orders(id integer PRIMARY KEY, item_name text, qty integer, ordered_at timestamp);

id,item_name,qty,ordered_at
1,商品1,2,2026-01-01 00:01:00
2,商品2,3,2026-01-01 00:02:00
...

Python（psycopg 3）

pip install "psycopg[binary]"

import csv, time
import psycopg

DSN = "host=127.0.0.1 dbname=t107 user=t107_app password=t107pw"
CSV_PATH = "orders.csv"

def read_rows():
    with open(CSV_PATH, encoding="utf-8", newline="") as f:
        r = csv.reader(f)
        next(r)  # ヘッダーを飛ばす
        return list(r)

def insert_loop(conn, rows):
    with conn.cursor() as cur:
        for row in rows:
            cur.execute("INSERT INTO orders VALUES (%s, %s, %s, %s)", row)

def insert_many(conn, rows):
    with conn.cursor() as cur:
        cur.executemany("INSERT INTO orders VALUES (%s, %s, %s, %s)", rows)

def copy_file(conn, rows=None):
    # CSVファイルをそのままサーバーへ流し込む（\copy と同じ仕組み）
    with conn.cursor() as cur, open(CSV_PATH, "rb") as f:
        with cur.copy("COPY orders FROM STDIN WITH (FORMAT csv, HEADER true)") as cp:
            while data := f.read(1024 * 64):
                cp.write(data)

def copy_rows(conn, rows):
    # Pythonで加工した行を1行ずつ送る
    with conn.cursor() as cur:
        with cur.copy("COPY orders (id, item_name, qty, ordered_at) FROM STDIN") as cp:
            for row in rows:
                cp.write_row(row)

rows = read_rows()
for name, fn in [("INSERTを1行ずつ", insert_loop), ("executemany", insert_many),
                 ("copy（ファイル）", copy_file), ("copy（write_row）", copy_rows)]:
    with psycopg.connect(DSN) as conn:
        conn.execute("TRUNCATE orders")
        t = time.perf_counter()
        fn(conn, rows)
        conn.commit()
        n = conn.execute("SELECT count(*) FROM orders").fetchone()[0]
    print(f"{name:<16} {n}件 {time.perf_counter() - t:6.2f}秒")

実行結果（1回目。2回目は下の比較表に記載）：

INSERTを1行ずつ      100000件  10.12秒
executemany      100000件   2.68秒
copy（ファイル）       100000件   0.40秒
copy（write_row）  100000件   0.44秒

write_row() は値をPythonのリストで渡すので、取り込み前に値を加工・検証したいときに便利です。 write_row() を使う場合はCSV形式ではなく既定の text 形式で送られ、エスケープはpsycopgが行います。

Node.js（pg + pg-copy-streams）

npm install pg pg-copy-streams

// load_node.mjs
import fs from "node:fs";
import { pipeline } from "node:stream/promises";
import pg from "pg";
import { from as copyFrom } from "pg-copy-streams";

const client = new pg.Client({
  host: "127.0.0.1", database: "t107", user: "t107_app", password: "t107pw",
});
await client.connect();

// COPY FROM STDIN（pg-copy-streams）
async function copyStream() {
  const ingest = client.query(copyFrom("COPY orders FROM STDIN WITH (FORMAT csv, HEADER true)"));
  await pipeline(fs.createReadStream("orders.csv"), ingest);
  console.log("  rowCount:", ingest.rowCount);
}

// 比較用の「1行ずつINSERT」関数（client.queryをループ）は省略
await client.query("TRUNCATE orders");
const t = performance.now();
await copyStream();
console.log(`pg-copy-streams ${((performance.now() - t) / 1000).toFixed(2)}秒`);
await client.end();

実行結果（1行ずつINSERTする関数を加えて同時に計測した1回目）：

INSERTを1行ずつ 12.32秒
  rowCount: 100000
pg-copy-streams 0.27秒

ストリームで流すので、ファイル全体をメモリに読み込まずに済みます。 大きなCSVでもメモリ使用量が増えにくい書き方です。

速度比較のまとめ（10万行）

同じマシン内で接続した結果で、共有環境のためINSERTの時間には揺れがあります。ネットワーク越しの接続では1行ごとの往復が増えるため、差はさらに開く傾向があります。 また、\copy とサーバー側 COPY の差は今回の条件ではごくわずかでした。

よくある質問

Q. Dockerやクラウドのマネージドデータベースでも使えますか？ A. 手元のファイルを \copy で送る方法なら使えます。サーバー側の COPY ... FROM 'ファイル' は、マネージドサービスではサーバーのファイルシステムに触れないため基本的に使えません。Dockerのコンテナ内で COPY を使う場合は、ファイルをコンテナ内（ボリューム）に置き、コンテナ内のパスを指定します。

Q. エラーの行だけ飛ばして取り込めますか？ A. PostgreSQL 17以降は ON_ERROR ignore で型変換エラーの行を飛ばせます。16以前では、全列 text の一時テーブルに取り込み、SQLで正しい行だけを本テーブルへ入れる方法が確実です。

Q. Windowsのpsqlで \copy のパスはどう書きますか？ A. \copy items FROM 'C:/work/items.csv' WITH (FORMAT csv, HEADER true) のようにスラッシュ区切りで書き、シングルクォートで囲みます。

まとめ

• COPY はサーバー側のファイル、\copy は手元のファイルを扱う。 迷ったら \copy
• 一般ユーザーの COPY ... 'ファイル' には pg_read_server_files / pg_write_server_files が必要
• CSVでは FORMAT csv を必ず付ける。 列リストで列順のずれを吸収できる
• エラーの CONTEXT に行番号・列名が出るので、そこから直す
• Shift_JISは ENCODING 'SJIS'、BOMは HEADER true なら影響なし、ヘッダーなしなら削除
• PostgreSQL 16の DEFAULT、17の ON_ERROR で取り込みの幅が広がる
• アプリからはドライバのCOPYを使うと、10万行で1行ずつINSERTの20倍以上速い

株式会社RJCでは、データベースの扱い方を仲間と学び合っています。こうした知識を共有しながら、日々技術を磨いています。

株式会社RJC ― SI事業・SES事業・AI駆動開発。RJCは一緒に成長を楽しめる会社です。