CSV to SQL: INSERT Statements, COPY Recipes, and the NULL Trap

Two ways to get a CSV into a database: generate INSERT statements, or use the bulk loader. The generated INSERTs are for portability and review; the bulk loader is for anything over ~10k rows. Everything below applies to both.

The generated-INSERT path

The converter emits one statement per record from the first row's header:

INSERT INTO records (id, name, email, role) VALUES ('1', 'Sarah Connor', '[email protected]', 'admin');

Three behaviours worth knowing before you run the output against anything:

• Values stay quoted unless they round-trip as a finite number. 42 becomes 42, 1e5 becomes 100000, ' is doubled to ''. The number test is on the trimmed cell, so a quoted "01234" still arrives as 01234 and is emitted as 1234. Every leading-zero column is at risk; see patterns and gotchas.
• Non-identifier column names get double-quoted. user id → "user id". Case is then significant in Postgres and unquoted identifiers fold to lower case — so "Order" and order are different columns and one of them is probably not yours.
• A short row emits a short VALUES list. INSERT INTO records (a,b,c) VALUES (1,2); is rejected by the database, which is the correct outcome: the alternative is padding with invented empty strings, and the row-count warning is what tells you which rows to fix.

Practical batching, because one statement per row means one parse per row:

INSERT INTO records (id, name) VALUES ('1','a'), ('2','b'), ('3','c');   -- 500-1000 rows per statement
-- or wrap in a transaction; a 100k-row file with autocommit is minutes slower
BEGIN; … COMMIT;

Add ON CONFLICT (id) DO NOTHING (Postgres) or ON DUPLICATE KEY UPDATE (MySQL) to make a re-run safe, and remember those two clauses have different failure semantics — the two clauses have different failure semantics, so test the conflict path explicitly.

Postgres: COPY is 10-100× faster

COPY records (id, name, email, role)
FROM STDIN WITH (FORMAT csv, HEADER true, DELIMITER ',', QUOTE '"', NULL '', ENCODING 'UTF8');

• NULL '' — the trap. The default NULL marker in COPY … CSV is the unquoted two-character sequence \N. With defaults, an empty field loads as an empty string and a field containing \N loads as NULL. Most people who hit this want NULL ''; most people who need the distinction have to write it explicitly the other way.
• HEADER true skips the first line. It does not validate that the header names match your column list — the column list you wrote is the truth.
• Server-side paths require superuser or the pg_read_server_files role; use \copy from psql for client-side file reads, or pipe with cat f.csv | psql -c "COPY … FROM STDIN …".
• A bad row aborts the whole COPY. The standard pattern is a staging table of text columns, COPY everything in (nothing to coerce, nothing to fail), then INSERT … SELECT with explicit casts and a WHERE that reports rejects.

MySQL / MariaDB: LOAD DATA

LOAD DATA INFILE '/tmp/f.csv'
INTO TABLE records
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '\\'
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES
(id, @name, @phone) SET phone = NULLIF(@phone, '');

Differences that bite: MySQL's default NULL representation is \N in both directions; OPTIONALLY ENCLOSED is required for quoted fields; LINES TERMINATED BY must match the file's actual line ending; and in non-strict sql_mode a column-count mismatch silently fills missing columns with defaults instead of erroring. Check sql_mode before trusting a load that "worked", and use LOAD DATA LOCAL INFILE (with local_infile enabled client-side) when you cannot write to the server's filesystem.

sqlite3

.mode csv
.import --skip 1 f.csv records        -- 3.32+; older: .import f.csv records (header becomes a row)

sqlite is dynamically typed, so nothing is coerced and everything is stored as text unless you declare a column affinity — which means a 01234 stays intact in sqlite and is destroyed by the Postgres integer column across the street.

The five mistakes in every load

1. Empty string vs NULL. Decide per column, state it in the mapping, and use the loader's NULL marker rather than post-hoc UPDATE … SET col = NULL WHERE col = ''.
2. Text-only headers as identifiers. Date, Order, User are reserved in some dialects; quote them or rename them.
3. Dates parsed by the client, not the database. A 2026/1/5 value means January 5 or May 1 depending on who read it first. Set DateStyle (PG) / parse with an explicit format, and prefer ISO 2026-01-05 on the way out of the producer.
4. Type inference eating leading zeros — the same bug in every path, documented with the mechanism on best practices.
5. No row-count reconciliation. Row count in the file (not line count — quoted newlines) must equal rows inserted, and the check belongs in the same script that does the load.

For a one-off, the converter here plus a transaction is fine. For a nightly job, use the loader, a staging table, and the reconciliation — and see large CSV files for where the browser-based path stops being the right tool.