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
INSERTwith several rows inVALUESis much faster than one statement per row. - Columns you leave out get their default: the identity
id,created_at = now()andplan = 'free'. RETURNINGsends 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 3means three rows were inserted (the0is 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
| Task | SQL |
|---|---|
| Insert several rows | INSERT INTO t (a, b) VALUES (...), (...); |
| Get generated values | ... RETURNING id; |
| Insert from a query | INSERT INTO t SELECT ...; |
| Update | UPDATE t SET col = ... WHERE ...; |
| Delete | DELETE FROM t WHERE ...; |
| Remove all rows fast | TRUNCATE t; |
| Skip duplicates | ON CONFLICT (key) DO NOTHING |
| Upsert | ON CONFLICT (key) DO UPDATE SET col = EXCLUDED.col |
| Try safely | BEGIN; ... 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?
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?
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?
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
RETURNINGto get generated values back. - Always check the
WHEREofUPDATEandDELETE; try risky changes betweenBEGINandROLLBACK. ON CONFLICT DO NOTHING / DO UPDATEgives atomic, race-free upserts.- Identity and sequence values have gaps; that is by design.
- Use
\copy(client files) orCOPY(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)".