Lesson 06 beginner

Inserting, Updating and Deleting Data

Fill Orbit with users and notes, get generated values back with RETURNING, update and delete rows safely inside a transaction, write upserts with ON CONFLICT, understand why identity values have gaps, and bulk-load CSV files with \copy and COPY.

Tables are empty boxes until you put data in them. In this lesson you load the data that every following lesson queries, and learn the four statements that change data: INSERT, UPDATE, DELETE and the upsert form of INSERT. Follow along in the orbit database, connected as the orbit role.

Insert the users

INSERT INTO users (email, name) VALUES
  ('ana@orbit.dev',  'Ana Silva'),
  ('ben@orbit.dev',  'Ben Carter'),
  ('chen@orbit.dev', 'Chen Wei')
RETURNING id, email, plan;
 id |     email      | plan
----+----------------+------
  1 | ana@orbit.dev  | free
  2 | ben@orbit.dev  | free
  3 | chen@orbit.dev | free
(3 rows)

INSERT 0 3
  • One INSERT with several rows in VALUES is much faster than one statement per row.
  • Columns you leave out get their default: the identity id, created_at = now() and plan = 'free'.
  • RETURNING sends back the rows as they were stored, including generated values, so your application does not need a second query to learn the new id.
  • The tag INSERT 0 3 means three rows were inserted (the 0 is a historical leftover).

Note: If you see different ids, you probably ran an insert before. Start clean with TRUNCATE notes, users RESTART IDENTITY; and insert again, so your results match the course.

Insert the notes

Each note needs the id of its author. Here the creation times are given explicitly, so the queries in later lessons give you exactly the same results as shown:

INSERT INTO notes (user_id, title, body, pinned, created_at) VALUES
  (1, 'Shopping list',    'eggs, milk, coffee',                   true,  '2026-10-01 09:00+00'),
  (1, 'Deploy checklist', 'run migrations, then restart the api', false, '2026-10-03 14:30+00'),
  (2, 'Book ideas',       'a database detective story',           false, '2026-10-05 20:15+00'),
  (2, 'Postgres tips',    'always set lock_timeout before DDL',   true,  '2026-10-12 08:45+00'),
  (1, 'Meeting notes',    'discuss the backup policy',            false, '2026-11-02 10:00+00');
INSERT 0 5

Ana now has three notes, Ben two and Chen none, which will matter when you join tables in lesson 8.

To copy rows from a query instead of literal values, use INSERT ... SELECT:

INSERT INTO notes_archive (id, user_id, title)
SELECT id, user_id, title FROM notes WHERE created_at < '2026-01-01';

Update rows

UPDATE changes columns of every row that matches the WHERE clause. Ben upgrades to the pro plan:

UPDATE users SET plan = 'pro' WHERE email = 'ben@orbit.dev'
RETURNING id, name, plan;
 id |    name    | plan
----+------------+------
  2 | Ben Carter | pro
(1 row)

UPDATE 1

You can set several columns at once and use the old values in expressions, for example SET title = upper(title), pinned = NOT pinned.

The forgotten WHERE

An UPDATE or DELETE without WHERE changes every row. Protect yourself by trying risky changes inside a transaction first:

BEGIN;
UPDATE notes SET body = '';
BEGIN
UPDATE 5

Five rows instead of the one you meant. Nothing is lost yet, because the transaction is still open:

ROLLBACK;

ROLLBACK undoes everything since BEGIN. When the counts look right, use COMMIT instead. Lesson 12 covers transactions in depth.

Delete rows

INSERT INTO notes (user_id, title) VALUES (3, 'Temporary') RETURNING id;
 id
----
  6
(1 row)

INSERT 0 1
DELETE FROM notes WHERE id = 6 RETURNING title;
   title
-----------
 Temporary
(1 row)

DELETE 1

DELETE ... RETURNING is a neat way to move rows: combine it with INSERT in one statement (lesson 9 shows the pattern with a CTE). To remove all rows quickly, TRUNCATE notes; is much faster than DELETE FROM notes;, because it does not touch each row.

Upserts: insert or update

A sign-up form may send a user who already exists. With a unique constraint on email, a plain INSERT fails. ON CONFLICT tells Postgres what to do instead.

Ignore duplicates:

INSERT INTO users (email, name) VALUES ('ana@orbit.dev', 'Ana Silva')
ON CONFLICT (email) DO NOTHING;
INSERT 0 0

Or update the existing row. Chen buys the pro plan through a checkout that always sends the full record:

INSERT INTO users (email, name, plan) VALUES ('chen@orbit.dev', 'Chen Wei', 'pro')
ON CONFLICT (email) DO UPDATE SET plan = EXCLUDED.plan
RETURNING id, name, plan;
 id |   name   | plan
----+----------+------
  3 | Chen Wei | pro
(1 row)

INSERT 0 1

EXCLUDED is the row you tried to insert. The whole operation is atomic: even with many clients doing the same thing at once, you never get a duplicate or a “duplicate key” error. Add a WHERE to DO UPDATE to skip updates that would change nothing.

MERGE, available since PostgreSQL 15, is the SQL-standard alternative when you need to insert, update and delete based on a source table. ON CONFLICT remains the simplest tool for single-row upserts.

Why ids have gaps

An identity column takes its numbers from a sequence named <table>_<column>_seq. Ask it which value it handed out last:

SELECT last_value FROM users_id_seq;
 last_value
------------
          5
(1 row)

Only three users exist, yet the sequence is at 5. Each of the two upserts above took a value from the sequence before discovering the conflict, and those values are gone. The same happens when a transaction that inserted rows rolls back. The next new user will get id 6.

Sequences never go backwards. Handing out numbers without waiting for other transactions to finish is what lets many clients insert at the same time. Gaps are normal; never rely on ids being consecutive.

Bulk loading with \copy and COPY

For thousands or millions of rows, COPY is many times faster than INSERT. Suppose a partner sends a CSV of contacts:

email,name
dana@example.com,Dana Kim
eli@example.com,Eli Novak

Load it into a staging table first, check it, then move the good rows over:

CREATE TABLE import_users (email text, name text);
\copy import_users FROM 'contacts.csv' WITH (FORMAT csv, HEADER)
SELECT count(*) FROM import_users;

\copy is a psql command: it reads the file on your machine and streams it to the server. The SQL command COPY ... FROM '/path/file.csv' reads a file on the server’s disk and needs extra privileges. Both work in the other direction too:

\copy (SELECT id, email, plan FROM users ORDER BY id) TO 'users.csv' WITH (FORMAT csv, HEADER)

Since PostgreSQL 17, COPY can skip rows with bad values instead of aborting the whole load: add ON_ERROR ignore to the options. Drop the staging table when you are done:

DROP TABLE import_users;

Quick reference

TaskSQL
Insert several rowsINSERT INTO t (a, b) VALUES (...), (...);
Get generated values... RETURNING id;
Insert from a queryINSERT INTO t SELECT ...;
UpdateUPDATE t SET col = ... WHERE ...;
DeleteDELETE FROM t WHERE ...;
Remove all rows fastTRUNCATE t;
Skip duplicatesON CONFLICT (key) DO NOTHING
UpsertON CONFLICT (key) DO UPDATE SET col = EXCLUDED.col
Try safelyBEGIN; ... ROLLBACK;
Import a local CSV\copy t FROM 'file.csv' WITH (FORMAT csv, HEADER)

Exercise

An UPDATE reports UPDATE 5000 when you expected UPDATE 1. You ran it after BEGIN. What should you do?

  1. COMMIT, then run a second UPDATE to fix the other rows
  2. Nothing, Postgres only applies the first matching row
  3. ROLLBACK, fix the WHERE clause and run it again
  4. TRUNCATE the table and reload it from a backup
Show answer

Answer: C. Inside an open transaction nothing is permanent yet. ROLLBACK undoes the UPDATE completely, so you can correct the statement before committing.

Exercise

What does EXCLUDED refer to in INSERT … ON CONFLICT DO UPDATE SET plan = EXCLUDED.plan?

  1. The row that already exists in the table
  2. The row that the INSERT tried to add
  3. Rows deleted by the statement
  4. A system table that logs conflicts
Show answer

Answer: B. EXCLUDED holds the proposed row. The existing row is referred to by the table name, for example users.plan.

Exercise

A user reports that order ids jump from 41 to 44. What is the most likely explanation?

  1. Two inserts took values from the sequence but rolled back or hit a conflict
  2. Rows 42 and 43 were corrupted
  3. The identity column was reset
  4. Postgres skips numbers on purpose to spread rows across disk pages
Show answer

Answer: A. Sequence values are handed out immediately and never returned, so failed or rolled-back inserts leave gaps. That is expected behaviour.

Key takeaways

  • Insert many rows per statement and use RETURNING to get generated values back.
  • Always check the WHERE of UPDATE and DELETE; try risky changes between BEGIN and ROLLBACK.
  • ON CONFLICT DO NOTHING / DO UPDATE gives atomic, race-free upserts.
  • Identity and sequence values have gaps; that is by design.
  • Use \copy (client files) or COPY (server files) for bulk loads, ideally into a staging table first.

FAQ

Is it safe to run UPDATE on a table while the application is using it?

Yes. Updates lock only the rows they change, and readers are never blocked by writers in Postgres. Large updates can still slow things down and produce many dead rows, so split them into batches of a few thousand rows.

How do I insert a value into a GENERATED ALWAYS identity column?

Add OVERRIDING SYSTEM VALUE after the column list, for example when migrating existing ids. Afterwards move the sequence past the highest id with SELECT setval(pg_get_serial_sequence('users', 'id'), (SELECT max(id) FROM users));.

Why does my upsert need a unique constraint?

ON CONFLICT (email) relies on a unique index on email to detect the conflict reliably. Without one, Postgres has no way to know two rows are duplicates and rejects the statement.

What is the fastest way to delete millions of old rows?

Delete in batches (for example 10,000 rows per statement) so locks and the write-ahead log stay manageable, or, better, partition the table by time and drop old partitions, which is instant.

Can COPY read compressed files?

Server-side COPY can read from a program: COPY t FROM PROGRAM 'gunzip -c /data/file.csv.gz' WITH (FORMAT csv), which needs superuser or the pg_execute_server_program role. With psql, pipe the data in: gunzip -c file.csv.gz | psql -c "\copy t FROM STDIN WITH (FORMAT csv)".

↑ ↓ navigate Enter open Esc close