Insert and COPY

Introduction

INSERT adds rows to your tables. PostgreSQL extends basic inserts with RETURNING (get new rows back in one round trip), COPY for fast bulk loads, and ON CONFLICT for upserts. This chapter seeds users and posts from Databases, Schemas, and Types so later query chapters have real data.

Prerequisites

Databases, Schemas, and Types
Tables users and posts in shop_dev or hello_demo
Connected via psql

Basic INSERT

Insert one row with explicit columns:

sql

INSERT INTO users (email, display_name)
VALUES ('ada@example.com', 'Ada Lo');

Code explanation:

Column order matches VALUES order
Omitted columns use DEFAULT or NULL if allowed
id and created_at fill from IDENTITY and DEFAULT NOW()

Insert with optional columns:

sql

INSERT INTO users (email, display_name, is_active)
VALUES ('bob@example.com', 'Bob Chen', TRUE);

Verify:

sql

SELECT id, email, display_name, created_at FROM users;

Multi-Row INSERT

One statement, many rows—fewer round trips than repeated inserts:

sql

INSERT INTO users (email, display_name) VALUES
  ('carol@example.com', 'Carol Wu'),
  ('dan@example.com', 'Dan Park'),
  ('eve@example.com', 'Eve Kim');

Compare with MySQL inserting data—syntax is nearly identical.

INSERT With RETURNING

PostgreSQL can return inserted rows without a second SELECT:

sql

INSERT INTO users (email, display_name)
VALUES ('frank@example.com', 'Frank Lee')
RETURNING id, email, created_at;

Use in apps to grab the new primary key:

sql

INSERT INTO posts (user_id, title, body, published_at)
VALUES (
  (SELECT id FROM users WHERE email = 'ada@example.com'),
  'Hello PostgreSQL',
  'First post about learning SQL.',
  NOW()
)
RETURNING id, title;

Code explanation:

RETURNING * or a column list comes back as a result set—psycopg and ORMs map this cleanly
MySQL has no direct equivalent—you run SELECT LAST_INSERT_ID() or a follow-up query

INSERT From SELECT

Copy rows from a query:

sql

CREATE TABLE users_archive (LIKE users INCLUDING ALL);
 
INSERT INTO users_archive (email, display_name, is_active, created_at)
SELECT email, display_name, is_active, created_at
FROM users
WHERE is_active = FALSE;

Useful for backups, reporting snapshots, or bulk moves between schemas.

Sample Posts Data

Assume users.id 1 = Ada and 2 = Bob (adjust if your ids differ):

sql

Check:

sql

SELECT id, user_id, title, published_at, tags FROM posts;

ON CONFLICT (Upsert Preview)

Full upsert patterns live in Update, Delete, and Upsert. Quick taste—skip duplicate emails:

sql

INSERT INTO users (email, display_name)
VALUES ('ada@example.com', 'Ada Duplicate')
ON CONFLICT (email) DO NOTHING;

Update on conflict:

sql

INSERT INTO users (email, display_name)
VALUES ('ada@example.com', 'Ada Lo Updated')
ON CONFLICT (email) DO UPDATE
SET display_name = EXCLUDED.display_name;

Code explanation:

ON CONFLICT requires a unique or primary key constraint on email
EXCLUDED refers to the row that would have been inserted—MySQL uses VALUES(column) in ON DUPLICATE KEY UPDATE

COPY for Bulk Load

COPY is PostgreSQL's fast path for CSV and text files—often used in ETL and seed scripts.

Create a CSV on your host (example users_seed.csv):

csv

email,display_name
grace@example.com,Grace Park
henry@example.com,Henry Ng

From psql, load into users:

sql

COPY users (email, display_name)
FROM '/tmp/users_seed.csv'
WITH (FORMAT csv, HEADER true);

Docker note—the file path must be visible inside the server process. Common patterns:

bash

# Copy file into container, then COPY from container path
docker cp users_seed.csv postgres:/tmp/users_seed.csv

sql

COPY users (email, display_name)
FROM '/tmp/users_seed.csv'
WITH (FORMAT csv, HEADER true);

Export with COPY ... TO:

sql

COPY (SELECT id, email FROM users ORDER BY id)
TO '/tmp/users_export.csv'
WITH (FORMAT csv, HEADER true);

Warning

Server-side paths

COPY reads/writes on the database server, not your laptop path unless you use \copy in psql (client-side). For local dev in Docker, copy files into the container or use \copy.

psql \copy (Client-Side)

From your machine into the server—no superuser file access on the server:

bash

# In psql
\copy users (email, display_name) FROM 'users_seed.csv' WITH (FORMAT csv, HEADER true)

Code explanation:

\copy streams through the psql client—ideal for developers
COPY without backslash runs inside the server—ideal for pg_dump-scale loads and cloud storage integrations

Insert Best Practices

Habit	Why
List column names explicitly	Survives `ALTER TABLE` adding columns
Batch rows in one `INSERT`	Less latency than hundreds of singles
Use `RETURNING` in APIs	One round trip for id + row
Use `COPY` / `\copy` for large CSV	Much faster than row-by-row inserts
Wrap bulk loads in a transaction	`BEGIN;` … `COPY` … `COMMIT;` for all-or-nothing

FAQ

INSERT vs COPY—which is faster?

Row INSERT is flexible; COPY is optimized for bulk. Use INSERT for app requests; COPY for migrations and analytics loads.

Why did ON CONFLICT fail?

You need a unique constraint or primary key on the conflict target—ON CONFLICT (email) requires UNIQUE (email).

RETURNING with multiple rows?

Yes—multi-row INSERT ... VALUES (...), (...) returns all inserted rows when you RETURNING.

Can I INSERT into JSONB?

Yes—cast literals: '{"a": 1}'::jsonb or pass parameters from your driver. Details in JSONB and Advanced Types.

COPY permission denied?

COPY FROM file on server often needs superuser or pg_read_server_files role. Prefer \copy in dev.

How does this compare to MySQL LOAD DATA?

MySQL LOAD DATA INFILE is the rough equivalent of COPY. PostgreSQL's COPY syntax is standard in the ecosystem and works well with psql and cloud bulk loaders.