INSERT, RETURNING & UPSERT

Basic insert

INSERT INTO students (roll_no, name_en, name_bn, email, age, admitted_on)
VALUES ('CSE-101', 'Rahim Uddin', 'রহিম উদ্দিন', 'rahim@example.com', 20, '2024-01-15');

Multiple rows

INSERT INTO students (roll_no, name_en, age, admitted_on) VALUES
  ('CSE-102', 'Karim Ali',    21, '2024-01-15'),
  ('CSE-103', 'Fatema Begum', 19, '2024-02-01'),
  ('CSE-104', 'Nusrat Jahan', 22, '2024-02-01');

RETURNING

This is one of the nicest features in Postgres: any INSERT, UPDATE or DELETE can return the affected rows, so you do not need a second query.

INSERT INTO students (roll_no, name_en, admitted_on)
VALUES ('CSE-105', 'Tanvir', current_date)
RETURNING id, created_at;

UPDATE students SET cgpa = cgpa + 0.1 WHERE department = 'CSE'
RETURNING id, name_en, cgpa;

DELETE FROM students WHERE cgpa < 2.0 RETURNING *;

Insert from a query

INSERT INTO alumni (name_en, department, graduated_on)
SELECT name_en, department, current_date
FROM students
WHERE cgpa >= 3.5;

UPSERT with ON CONFLICT

-- do nothing if the key already exists
INSERT INTO students (roll_no, name_en, admitted_on)
VALUES ('CSE-101', 'Duplicate', current_date)
ON CONFLICT (roll_no) DO NOTHING;

-- update the existing row instead
INSERT INTO students (roll_no, name_en, admitted_on)
VALUES ('CSE-101', 'Rahim U.', current_date)
ON CONFLICT (roll_no) DO UPDATE
SET name_en    = EXCLUDED.name_en,
    updated_at = now()
RETURNING id;

-- conditional upsert
INSERT INTO page_views (page, views) VALUES ('/home', 1)
ON CONFLICT (page) DO UPDATE
SET views = page_views.views + EXCLUDED.views
WHERE page_views.locked = false;

EXCLUDED is the row you tried to insert; the table name refers to the row already stored. The conflict target must match a unique index or constraint.

Bulk loading with COPY

COPY is dramatically faster than row-by-row inserts — often 10–50× for large files.

-- server side (needs superuser or pg_read_server_files, path is on the server)
COPY students (roll_no, name_en, age, admitted_on)
FROM '/var/lib/postgresql/students.csv'
WITH (FORMAT csv, HEADER true);

-- export
COPY (SELECT * FROM students WHERE cgpa > 3.5) TO '/tmp/top.csv' WITH (FORMAT csv, HEADER);

# client side, from psql — no server file access needed
\copy students(roll_no,name_en,age,admitted_on) FROM 'students.csv' CSV HEADER
\copy (SELECT * FROM students) TO 'out.csv' CSV HEADER

Faster bulk loads

1. Load into an UNLOGGED staging table, then insert into the real table.
2. Drop indexes and foreign keys before the load, recreate them after.
3. Wrap the whole load in one transaction.
4. Raise maintenance_work_mem before rebuilding indexes.
5. Run ANALYZE afterwards so the planner has fresh statistics.