Fix Missing Headers in PostgreSQL CSV Export (2026)

A developer on Stack Overflow ran this exact command:

COPY products_273 to '/tmp/products_199.csv' delimiters',';

They got a CSV file, but column headers were nowhere to be seen. (The command contains a syntax error — delimiters is not a valid keyword, so in modern PostgreSQL it would fail outright; the poster likely ran a corrected version without HEADER.) Regardless, the root cause is simple: the HEADER option was missing. The fix: use COPY products_273 TO '/tmp/products_199.csv' WITH (FORMAT CSV, HEADER); on the server side, or \COPY products_273 TO 'products_199.csv' CSV HEADER; inside psql to write to your local machine.

The error, decoded

The missing HEADER leads to a CSV file where the first row is data, not column names. The desired output would look like this:

id,name,price   <-- this line never appears
1,Widget,9.99
2,Gadget,14.50

Two things go wrong in the original statement:

• Invalid keyword delimiters — the correct syntax is DELIMITER ',' or, for CSV, just use FORMAT CSV which defaults to comma. In any supported PostgreSQL version, delimiters causes a syntax error; if a file was produced, it means a different command was used.
• Missing HEADER — Even with a correct delimiter, COPY TO writes only data rows by default. You must ask for column names explicitly with the HEADER option.

Why COPY skips headers unless you ask

COPY is designed as a low-level bulk transfer tool. Its default behaviour is to output exactly the data, row by row, so that the output can be re-imported with COPY FROM without any extra parsing. The header line is an optional decoration controlled by the HEADER boolean toggle; the server never inserts it automatically. The official documentation lists HEADER as an option that “Specifies that the file contains a header line with the column names.” Without it, even a valid FORMAT CSV writes only the row values.

This design is intentional: when you chain COPY commands for data migration, an unexpected header in the middle of a pipe would break the import. Knowing that, you explicitly add HEADER whenever you need a human-readable or tool-friendly CSV with column labels.

The fix: correct syntax for every export scenario

Server-side export with headers

Replace the original command with a standard COPY … WITH (FORMAT CSV, HEADER):

COPY products_273 TO '/tmp/products_199.csv' WITH (FORMAT CSV, HEADER);

If your default delimiter is already a comma, DELIMITER ',' is optional; the FORMAT CSV line alone switches the output style. The file is written on the server filesystem, so you need filesystem permissions (pg_write_server_files role or superuser) unless you use a path like /tmp that the server process can write to.

Client-side psql export (local machine)

When you need the CSV file on your local machine, use psql’s built-in \COPY command. It behaves exactly like COPY TO but reads/writes files client-side:

psql -d mydb -c "\COPY products_273 TO 'products_199.csv' CSV HEADER;"

Inside an interactive psql session, drop the -c wrapper and omit the semicolon at the end — \COPY is a meta-command and a trailing semicolon can cause syntax issues:

\COPY products_273 TO 'products_199.csv' CSV HEADER

The CSV HEADER keywords are the equivalent of the server-side WITH (FORMAT CSV, HEADER). No superuser privileges are needed because the file operation happens on your local machine.

Export only selected columns

You can export a subset of columns — or the result of any query — by wrapping the query in parentheses:

COPY (SELECT id, name, price FROM products_273) TO '/tmp/products_199.csv'
  WITH (FORMAT CSV, HEADER);

The same works with \COPY:

\COPY (SELECT id, name, price FROM products_273) TO 'products_199.csv' CSV HEADER

This way you control exactly which columns appear in the output, and their order is taken from the SELECT list, not the table definition. If you need to verify column names before writing the query, \d (or the equivalent in psql) lists the table structure so you can pick the right names.

Handling NULLs and special characters

Null values are exported as an empty string by default in CSV mode. That can make a downstream tool misinterpret missing data. Use the NULL option to choose a placeholder:

\COPY products_273 TO 'output.csv' CSV HEADER NULL '\\N'

For fields that contain commas, quotes, or newlines, FORMAT CSV automatically quotes them. You can force quoting on specific columns with FORCE_QUOTE:

\COPY products_273 TO 'output.csv' CSV HEADER FORCE_QUOTE (name, description);

Check your PostgreSQL version before using FORCE_QUOTE — it was added in 9.4 — and use this guide to confirm the version you’re running.

Two patterns that still trip you up

Variant A — “Permission denied” on the server file

If you run COPY … TO '/some/protected/path/file.csv' without the proper role, PostgreSQL returns:

ERROR:  must be superuser or a member of the pg_write_server_files role to COPY to a file

The quickest fix is to switch to \COPY and write the file locally. If you must use server-side COPY, write to /tmp or assign the pg_write_server_files role to your user:

GRANT pg_write_server_files TO your_user;

Alternatively, pipe the output to STDOUT and redirect in psql:

psql -d mydb -c "\COPY products_273 TO STDOUT CSV HEADER" > products_199.csv

This avoids filesystem permission issues entirely because the server sends the data over the connection.

Variant B — The CSV opens with scrambled columns in Excel

Excel sometimes misinterprets the delimiter when the file extension is .csv but the actual delimiter isn’t a comma. If you exported with a custom delimiter, use a .tsv extension for tabs or import the file explicitly with the correct separator. Also ensure the file is saved with a byte order mark (BOM) if your data contains non‑ASCII characters.

Exporting JSON and JSONB data with headers

PostgreSQL offers two JSON data types: json (exact text storage) and jsonb (decomposed binary, indexable). When you need to export these columns to CSV with headers, you typically extract fields using the -> and ->> accessors, filter rows with the containment operator @>, and possibly expand nested arrays/objects.

Extracting fields with -> and ->> accessors

The -> operator returns a JSON object field as jsonb (or json). The ->> operator returns the field as text, which is what you usually want in a CSV:

\COPY (SELECT id, data->>'name' AS name, data->>'price' AS price FROM orders) TO 'orders.csv' CSV HEADER

This produces a clean header row with id, name, price.

Filtering with containment operator @>

If your jsonb column contains a status field, you can use the containment operator @> to select rows where the JSON matches a condition:

SELECT * FROM orders WHERE data @> '{"status": "active"}';

Wrap that in a \COPY query to export only active orders. The @> operator checks whether the left jsonb contains the right jsonb. For large tables, this becomes slow without proper indexing.

Indexing with GIN and jsonb_path_ops

To speed up containment queries, create a GIN index on the jsonb column. The jsonb_path_ops operator class is more efficient than the default jsonb_ops for @> because it supports only the containment operator and uses a smaller index:

CREATE INDEX idx_orders_data ON orders USING gin (data jsonb_path_ops);

This index accelerates your WHERE data @> ... conditions before the CSV export, especially when you only need a subset of rows.

Expanding JSON arrays with jsonb_array_elements

When a jsonb column stores an array, jsonb_array_elements turns each element into a row, enabling a tabular export:

\COPY (SELECT id, elem->>'item' AS item, elem->>'qty' AS quantity FROM orders, jsonb_array_elements(data->'items') AS elem) TO 'items.csv' CSV HEADER

Modifying JSON before export with jsonb_set

If you need to transform a value in the JSON column before writing it to CSV (e.g., update a field), use jsonb_set:

\COPY (SELECT id, jsonb_set(data, '{status}', '"archived"') AS new_data FROM orders) TO 'updated.csv' CSV HEADER

Note that jsonb_set returns jsonb, so you may still need ->> to extract a flat field.

Expanding object keys with jsonb_each

jsonb_each expands a JSON object into key-value pairs, which is useful for pivoting dynamic attributes into rows:

SELECT id, (kv).key, (kv).value FROM orders, jsonb_each(data->'attributes') AS kv;

You can then export this as CSV with headers.

By combining these tools, you can flatten complex JSON/JSONB data for CSV export exactly how you need it, with full control over column names and filtering.

Verify the fix

After running the corrected COPY or \COPY command, check the first two lines of the output:

head -n 2 products_199.csv

Expected output:

id,name,price
1,Widget,9.99

The header row with column names appears before the data. If it’s still missing, verify that you didn’t accidentally omit HEADER and that the command wasn’t overridden by an alias. Inside psql, you can run \set to check for any customisations that might affect \COPY behaviour.

FAQ

Why did my COPY command with delimiters produce a syntax error?

delimiters is not a valid keyword in the COPY dialect; PostgreSQL always treats it as a syntax error. As a result, the statement fails and no file is written. If you got a CSV file without headers, it's likely you ran a different command, such as psql's \COPY with CSV but missing HEADER, or a server-side COPY without HEADER. Always use WITH (FORMAT CSV, HEADER) for server-side or CSV HEADER for client-side to include column names.

Can I compress the CSV output on the fly with psql?

Yes — pipe \COPY … TO STDOUT into a compression tool:

psql -d mydb -c "\COPY products_273 TO STDOUT CSV HEADER" | gzip > products_199.csv.gz

This writes compressed output directly without an intermediate file on disk.

Related

• PostgreSQL DESCRIBE TABLE: The psql \d Equivalent
• Which version of PostgreSQL am I running? 3 ways to check
• PostgreSQL SHOW TABLES / DESCRIBE TABLE (psql + Supabase)

---

Originally published at https://www.iloveblogs.blog