Thuta Learning
Search
IntermediateData & Databasesbeginner

Import and Export with COPY

Relax. We'll talk through this in plain words — no textbook voice.

What you'll walk away with

  • Explain the core ideas behind Import and Export with COPY
  • Run the sample SQL or command and verify its output
  • Apply the technique to the Tutorial Platform and production scenarios

Build the mental model

`COPY` reads or writes the database server's filesystem and needs corresponding privilege. `psql`'s `\copy` uses the client filesystem, making local import/export convenient. Before bulk loading, define staging, encoding, delimiter, header, and error strategy, and use a transaction when atomicity is required.

Connect it to a real scenario

Load a categories CSV into a staging table first, validate and trim it, then merge into the production table. Staging makes bad-row diagnosis and repeatable cleanup safer than direct import.

Try the working example

sql
CREATE TABLE IF NOT EXISTS app.categories (
  category_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name text NOT NULL UNIQUE
);
CREATE TEMP TABLE category_stage (name text);

-- Run inside psql; the path is on the client machine:
\copy category_stage(name) FROM 'categories.csv' WITH (FORMAT csv, HEADER true)

INSERT INTO app.categories (name)
SELECT DISTINCT trim(name)
FROM category_stage
WHERE length(trim(name)) > 0
ON CONFLICT (name) DO NOTHING;

\copy (SELECT * FROM app.categories ORDER BY name) TO 'categories-export.csv' WITH (FORMAT csv, HEADER true)
You should see
Clean unique categories are imported and a verified CSV export is produced.

5-minute try-it

Create a CSV containing a malformed row and use a staging table to identify the problem.

One important caution

Copying untrusted CSV directly into production tables can create difficult cleanup and data-quality incidents.

PostgreSQL — COPY — PostgreSQL Global Development Group

Easy traps

  • Copying untrusted CSV directly into production tables can create difficult cleanup and data-quality incidents.
  • Validate sample code on a local or test database with recoverable backups before applying it to production data.

Exercise

Create a CSV containing a malformed row and use a staging table to identify the problem.

You'll know it worked when: Clean unique categories are imported and a verified CSV export is produced.

Import and Export with COPY | Thuta Learning