Export a PostgreSQL Table to a CSV File

PostgreSQL offers two commands for writing table data to a CSV file: the server-side COPY ... TO statement and the client-side psql \copy meta-command. COPY TO writes the file directly on the database host — ideal for large exports to a local filesystem — but requires superuser access. \copy streams the data to a file on your workstation over the psql connection and works with ordinary user privileges, making it the practical choice for scripts and CI pipelines.

Syntax

Server-side export with COPY TO:

COPY table_name [(column_list)]
TO '/absolute/path/to/output.csv'
[ DELIMITER ',' ]
[ CSV [ HEADER ] ];

Export a query result:

COPY (SELECT ...)
TO '/absolute/path/to/output.csv'
CSV HEADER;

Client-side export with \copy (run inside psql — no semicolon):

\copy table_name [(column_list)]
TO '/local/path/to/output.csv'
WITH (FORMAT csv, HEADER true)

Key options:

Practical Example

Create and populate an orders table:

CREATE TABLE orders (
  order_id    SERIAL PRIMARY KEY,
  product_sku VARCHAR(30) NOT NULL,
  quantity    INT         NOT NULL,
  unit_price  NUMERIC(10, 2) NOT NULL,
  status      VARCHAR(20) NOT NULL DEFAULT 'pending',
  placed_at   TIMESTAMPTZ NOT NULL DEFAULT now()
);

INSERT INTO orders (product_sku, quantity, unit_price, status, placed_at)
VALUES
  ('SKU-001', 3, 29.99, 'shipped',  '2026-03-01 09:00:00+00'),
  ('SKU-002', 1, 149.00, 'shipped', '2026-03-02 11:30:00+00'),
  ('SKU-003', 5, 9.50,  'pending',  '2026-03-03 14:15:00+00');

Export the entire table to a CSV file with a header row:

COPY orders
TO '/var/lib/postgresql/orders_export.csv'
DELIMITER ','
CSV HEADER;

COPY 3

Export only selected columns:

COPY orders (order_id, product_sku, status)
TO '/var/lib/postgresql/orders_partial.csv'
DELIMITER ','
CSV HEADER;

Export only shipped orders using a query:

COPY (
  SELECT order_id, product_sku, quantity, unit_price
  FROM orders
  WHERE status = 'shipped'
  ORDER BY placed_at
)
TO '/var/lib/postgresql/shipped_orders.csv'
CSV HEADER;

Exporting to Your Local Machine with \copy

When you need the file on your workstation or when connecting to a managed PostgreSQL service, use \copy inside psql. It accepts the same options but resolves the file path locally:

\copy (
  SELECT order_id, product_sku, quantity, unit_price, placed_at
  FROM orders
  ORDER BY placed_at DESC
) TO '/Users/you/orders_local.csv' WITH (FORMAT csv, HEADER true)

You can also pipe the result directly to a program using PROGRAM instead of a file path on the server side:

COPY orders TO PROGRAM 'gzip > /var/lib/postgresql/orders.csv.gz' CSV HEADER;

Testing with Vela

Before exporting production data — especially for data migration or audit reports — verify that your COPY query returns exactly the rows and columns you expect. Vela’s database branching makes this safe: create a branch from production, run the export query against the branch, and inspect the output file. You can iterate on your SELECT filter logic without touching the live database or generating incorrect reports.

Production Tips

• Use \copy in CI and deployment scripts to avoid hard-coding server-side file paths that vary between environments.
• Wrap multiple COPY TO commands in a single BEGIN / COMMIT block with REPEATABLE READ isolation when you need a consistent snapshot across several exported files.
• Add an ORDER BY clause inside the subquery to produce deterministic output — important for diffing exports across runs or comparing snapshots.
• Append a timestamp to the output filename (e.g., orders_20260401.csv) to prevent accidental overwrites of earlier exports.
• For large exports, run COPY during off-peak hours or against a read replica to avoid competing with OLTP traffic on the primary.
• Set NULL 'NULL' (or another sentinel string) so downstream consumers can distinguish empty strings from true SQL NULLs.