Inserting, Updating, and Deleting Data

Where We Left Off
INSERT One Row
Several Rows and RETURNING
Why Inserts Fail
Writing seed.sql
UPDATE and the WHERE Clause
The Danger of No WHERE
DELETE and Look Before You Leap
TRUNCATE
Upsert: INSERT ... ON CONFLICT
Try It Yourself

1. Where We Left Off

In the last chapter you created two tables in shopdb: customers and products. They have columns, types, and constraints, but they are empty. A table with no rows can answer no questions.

This chapter is about the DML side of SQL, the Data Manipulation Language: INSERT adds rows, UPDATE changes them, DELETE removes them. By the end you will have a seed.sql file that fills shopdb with the shop's starting data, and you can rebuild it in one command.

Open your psql session if it is not open already:

docker exec -it shopdb-pg psql -U postgres -d shopdb

If you did not save the CREATE TABLE statements from last chapter, here they are again. This is the content of shopdb/schema.sql so far:

CREATE TABLE customers (
  id         integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name       text NOT NULL,
  email      text NOT NULL UNIQUE,
  city       text,
  created_at timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE products (
  id       integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name     text NOT NULL,
  price    numeric(10,2) NOT NULL CHECK (price >= 0),
  stock    integer NOT NULL DEFAULT 0 CHECK (stock >= 0),
  category text
);

Note that products has a plain text category column for now. In chapter 6 we will replace it with a proper relationship to a categories table.

2. INSERT One Row

The basic form names the table, the columns you are filling, and the values, in the same order:

INSERT INTO customers (name, email, city)
VALUES ('Sokha Chan', 'sokha@example.com', 'Phnom Penh');

INSERT 0 1

The reply INSERT 0 1 means "one row inserted". (The 0 is a leftover from very old versions of Postgres. Ignore it.)

Look at what you did not provide: id and created_at. You do not need to. The identity column generates the id, and DEFAULT now() fills the timestamp. Check the result:

SELECT * FROM customers;

 id |    name    |       email       |    city    |          created_at
----+------------+-------------------+------------+-------------------------------
  1 | Sokha Chan | sokha@example.com | Phnom Penh | 2026-10-03 09:14:52.318+00
(1 row)

(The timestamp will be your current time. All outputs in this series are typical psql output, so spacing and times on your screen may differ a little.)

Two habits worth building now:

Always list the columns. INSERT INTO customers VALUES (...) works, but it depends on the exact column order. If someone adds a column later, your statement silently breaks or fills the wrong fields.
Text uses single quotes. 'Sokha Chan' is a string. Double quotes in SQL mean an identifier (a table or column name), not a string. To put a single quote inside a string, double it: 'O''Brien'.

A column you skip gets its DEFAULT, or NULL if it has none. The city column has no default and no NOT NULL, so this works and stores a NULL:

INSERT INTO customers (name, email)
VALUES ('Maly Ros', 'maly@example.com');

3. Several Rows and RETURNING

Sending one statement per row is slow and noisy. VALUES accepts a list of rows separated by commas:

INSERT INTO customers (name, email, city) VALUES
  ('Dara Kim',   'dara@example.com',   'Siem Reap'),
  ('Linda Vong', 'linda@example.com',  'Phnom Penh'),
  ('Vuthy Sok',  'vuthy@example.com',  'Battambang');

INSERT 0 3

This is a single statement, so it is all or nothing: if one of the three rows breaks a constraint, none of them is inserted. That is useful when loading data.

Often you need to know the id the database just generated, for example to insert related rows next. Do not run a second SELECT to find it. Add RETURNING, a Postgres feature that sends back columns from the rows you just changed:

INSERT INTO products (name, price, stock, category)
VALUES ('Wireless Mouse', 15.00, 40, 'Electronics')
RETURNING id, name, price;

 id |      name      | price
----+----------------+-------
  1 | Wireless Mouse | 15.00
(1 row)

INSERT 0 1

RETURNING also works with UPDATE and DELETE, and you can write RETURNING * to get every column. In a backend application it is how you learn the id of the row you just created, in one round trip.

A note on the id numbers. Identity columns use a sequence, a counter that only goes up. It never goes back, even if the insert fails or the row is deleted later. So ids can have gaps (1, 2, 4, 7) and that is normal. Never rely on ids being consecutive.

4. Why Inserts Fail

Constraints from chapter 3 now earn their keep. Try breaking each one and read the messages, because you will see them often.

Duplicate unique value. The email sokha@example.com already exists:

INSERT INTO customers (name, email)
VALUES ('Another Sokha', 'sokha@example.com');

ERROR:  duplicate key value violates unique constraint "customers_email_key"
DETAIL:  Key (email)=(sokha@example.com) already exists.

Missing required value. name is NOT NULL:

INSERT INTO customers (email) VALUES ('nobody@example.com');

ERROR:  null value in column "name" of relation "customers" violates not-null constraint
DETAIL:  Failing row contains (7, null, nobody@example.com, null, 2026-10-03 09:20:11.402+00).

Rule broken. A negative price fails the CHECK:

INSERT INTO products (name, price) VALUES ('Free Money', -5);

ERROR:  new row for relation "products" violates check constraint "products_price_check"
DETAIL:  Failing row contains (2, Free Money, -5.00, 0, null).

Setting a generated id yourself. The column is GENERATED ALWAYS, so Postgres refuses your value:

INSERT INTO customers (id, name, email)
VALUES (100, 'Manual Id', 'manual@example.com');

ERROR:  cannot insert a non-DEFAULT value into column "id"
DETAIL:  Column "id" is an identity column defined as GENERATED ALWAYS.
HINT:  Use OVERRIDING SYSTEM VALUE to override.

The first lines of each error name the constraint that was broken, and the DETAIL line shows the exact data. Reading these two lines solves most insert problems. Notice something else too: the failed inserts used up numbers from the identity sequences. The failing customer row above said id 7 even though only five customers exist. That is the gap behavior from the previous section, in action.

5. Writing seed.sql

Typing rows into psql is fine for exploring, but you do not want to re-type five customers and eight products every time. And after those failed inserts, your ids no longer match the ones used in the rest of this series. The fix is a seed script: a plain .sql file in your project that puts the database into a known state.

In your shopdb/ folder, create seed.sql:

-- shopdb/seed.sql
-- Resets the tables and loads the starting data.
TRUNCATE customers, products RESTART IDENTITY CASCADE;

INSERT INTO customers (name, email, city) VALUES
  ('Sokha Chan', 'sokha@example.com', 'Phnom Penh'),
  ('Dara Kim',   'dara@example.com',  'Siem Reap'),
  ('Linda Vong', 'linda@example.com', 'Phnom Penh'),
  ('Vuthy Sok',  'vuthy@example.com', 'Battambang'),
  ('Maly Ros',   'maly@example.com',  NULL);

INSERT INTO products (name, price, stock, category) VALUES
  ('Wireless Mouse',      15.00,  40, 'Electronics'),
  ('USB-C Cable',          5.50, 120, 'Electronics'),
  ('Mechanical Keyboard', 79.00,  15, 'Electronics'),
  ('SQL Cookbook',        32.00,  25, 'Books'),
  ('Clean Code',          28.50,  30, 'Books'),
  ('Desk Lamp',           22.00,   0, 'Home'),
  ('Coffee Mug',           6.00,  80, 'Home'),
  ('Notebook Pack',        9.50,  60, 'Books');

The first statement, TRUNCATE, empties the tables and restarts the id counters at 1 (more on it in section 9). The inserts then run in order, so ids are assigned 1 to 5 and 1 to 8. Because the order is fixed, the ids are the same every time. The rest of the series relies on this: "customer 1" will always be Sokha, "product 3" will always be the keyboard.

Now run the file against the database from your normal terminal, not from inside psql:

docker exec -i shopdb-pg psql -U postgres -d shopdb < seed.sql

TRUNCATE TABLE
INSERT 0 5
INSERT 0 8

The -i flag (instead of -it) tells Docker to pass your file through as input, and the < is the shell feeding seed.sql into the command. If you are already inside psql and your file is reachable there, the \i command from chapter 2 does the same job.

By default psql keeps going after an error, which can leave you with half-loaded data and a long scroll of messages. For scripts, ask it to stop at the first error:

docker exec -i shopdb-pg psql -U postgres -d shopdb \
  -v ON_ERROR_STOP=1 < seed.sql

Verify what you have:

SELECT id, name, price, stock, category FROM products ORDER BY id;

 id |        name         | price | stock |  category
----+---------------------+-------+-------+-------------
  1 | Wireless Mouse      | 15.00 |    40 | Electronics
  2 | USB-C Cable         |  5.50 |   120 | Electronics
  3 | Mechanical Keyboard | 79.00 |    15 | Electronics
  4 | SQL Cookbook        | 32.00 |    25 | Books
  5 | Clean Code          | 28.50 |    30 | Books
  6 | Desk Lamp           | 22.00 |     0 | Home
  7 | Coffee Mug          |  6.00 |    80 | Home
  8 | Notebook Pack       |  9.50 |    60 | Books
(8 rows)

From here on, whenever your data gets messy from experiments, run seed.sql again to go back to the known state. A database you can rebuild at will is a database you are not afraid to experiment on.

6. UPDATE and the WHERE Clause

UPDATE changes values in existing rows. The shape is:

UPDATE table_name
SET column = new_value [, column = new_value ...]
WHERE condition;

The WHERE clause decides which rows. Say the Wireless Mouse price rises to 16.00:

UPDATE products
SET price = 16.00
WHERE id = 1
RETURNING id, name, price;

 id |      name      | price
----+----------------+-------
  1 | Wireless Mouse | 16.00
(1 row)

UPDATE 1

The reply UPDATE 1 tells you how many rows changed. Read this number every time. If you expected one row and see UPDATE 0, your condition matched nothing (a wrong id, a typo). If you expected one and see UPDATE 340, stop and think.

The right side of SET can use the current values of the row, so you can compute from them. Restock the Desk Lamp (we have none) and record three Coffee Mug sales:

UPDATE products SET stock = stock + 10 WHERE name = 'Desk Lamp';
UPDATE products SET stock = stock - 3  WHERE name = 'Coffee Mug';

Change several columns at once by separating them with commas:

UPDATE customers
SET city = 'Kampot', name = 'Maly Ros-Chea'
WHERE email = 'maly@example.com';

Because stock has CHECK (stock >= 0), the database protects you from overselling by mistake:

UPDATE products SET stock = stock - 100 WHERE name = 'Mechanical Keyboard';

ERROR:  new row for relation "products" violates check constraint "products_stock_check"
DETAIL:  Failing row contains (3, Mechanical Keyboard, 79.00, -85, Electronics).

The update is rejected and the row stays at 15. This is the point of constraints: the rule lives in the database, so it holds no matter which application, script, or tired developer runs the statement.

Put the mouse price back so your data matches the seed again:

UPDATE products SET price = 15.00 WHERE id = 1;

7. The Danger of No WHERE

Now the most famous mistake in SQL. What does this do?

UPDATE products SET price = 0;

It does not ask "are you sure?". The WHERE clause is optional, and without it the statement applies to every row. All eight products now cost nothing.

Let's see it safely on a scratch copy, so shopdb stays clean. The statement CREATE TABLE ... AS SELECT builds a new table from a query:

CREATE TABLE products_scratch AS SELECT * FROM products;

UPDATE products_scratch SET price = 0;

SELECT 8
UPDATE 8

UPDATE 8 is the alarm bell: you changed all eight rows. In real life the number would be 80,000 and your phone would ring. The same goes for DELETE FROM products;, which removes every row.

Three habits protect you:

Write the WHERE first. Type UPDATE products ... WHERE id = 1, then go back and fill in the SET part. You will never forget the condition because it is already there.
Test with SELECT first. Turn the statement into a query with the same WHERE and look at the rows it would touch:
```
SELECT id, name, price FROM products WHERE category = 'Books';
```
Only when those are exactly the rows you mean, change SELECT ... to UPDATE ... SET ... and keep the same WHERE.
Use a transaction so you can undo. You will learn BEGIN and ROLLBACK properly in chapter 12. For now, remember that a database has an undo for mistakes like this, but only if you ask for it before running the statement.

Clean up the scratch table:

DROP TABLE products_scratch;

8. DELETE and Look Before You Leap

DELETE removes rows and has the same shape as UPDATE: a table and a WHERE.

DELETE FROM table_name WHERE condition;

Add a throwaway customer and then remove them, using the SELECT first habit:

INSERT INTO customers (name, email) VALUES ('Test Person', 'test@example.com')
RETURNING id;

 id
----
  6
(1 row)

INSERT 0 1

SELECT id, name FROM customers WHERE email = 'test@example.com';

 id |    name
----+-------------
  6 | Test Person
(1 row)

Exactly the one row we want. Now delete it, matching on the primary key, which is the safest way to target a single row:

DELETE FROM customers WHERE id = 6 RETURNING *;

RETURNING on a DELETE hands you the row you just removed, a handy record in case you deleted the wrong one. The reply ends with:

DELETE 1

Two things to remember about deleting:

Deleting is permanent. Unlike a trash folder, nothing is recoverable unless you have a backup or a transaction. Many real applications avoid deleting important rows at all (see "soft delete" in chapter 10).
Relationships can block it. Once orders reference customers (next chapter), you cannot delete a customer who still has orders, unless the relationship says otherwise. That protection is exactly what foreign keys are for.

9. TRUNCATE

If you want to empty a whole table, DELETE FROM products; works but is slow on large tables, because Postgres removes the rows one at a time. TRUNCATE throws them away in one step:

TRUNCATE products;

It is fast, but it is a bigger hammer than DELETE:

It takes no WHERE. It is always the whole table.
By default, it does not reset the identity counter. New rows keep counting from where the old ones left off. Add RESTART IDENTITY to start from 1 again.
It refuses to run on a table that other tables reference through foreign keys. Add CASCADE to also empty those tables. This is why your seed.sql says RESTART IDENTITY CASCADE: once orders exists, truncating customers must empty orders too, and CASCADE makes that explicit.

Treat TRUNCATE ... CASCADE like DELETE without a WHERE, only louder. It is perfect for a development seed script and a bad idea to type casually near real data.

If you just ran TRUNCATE products while experimenting, run seed.sql again to restore everything.

10. Upsert: INSERT ... ON CONFLICT

A common need: "add this row, but if it already exists, update it instead". Doing it by hand is awkward: run a SELECT, check the result in your program, then run either INSERT or UPDATE. Between those steps, somebody else could insert the same row.

Postgres does it in one atomic statement with ON CONFLICT, often called an upsert (update + insert). It relies on a unique constraint, here the one on email:

INSERT INTO customers (name, email, city)
VALUES ('Maly Ros', 'maly@example.com', 'Takeo')
ON CONFLICT (email)
DO UPDATE SET city = EXCLUDED.city
RETURNING id, name, city;

 id |     name      | city
----+---------------+-------
  5 | Maly Ros-Chea | Takeo
(1 row)

INSERT 0 1

Maly already existed (id 5), so instead of the duplicate key error you saw in section 4, the existing row was updated. EXCLUDED is a special name for "the row I tried to insert", so EXCLUDED.city is 'Takeo'. Only city was changed: the name in the table is still the Maly Ros-Chea from section 6, because DO UPDATE touches only the columns you list.

If you would rather leave the existing row alone and simply skip the insert, use DO NOTHING:

INSERT INTO customers (name, email)
VALUES ('Maly Ros', 'maly@example.com')
ON CONFLICT (email) DO NOTHING;

INSERT 0 0

INSERT 0 0 means zero rows inserted, and no error. This makes seed and import scripts safe to re-run. Put Maly's city back to NULL to match the seed, or just run seed.sql again, which also fixes the name:

UPDATE customers
SET city = NULL, name = 'Maly Ros'
WHERE email = 'maly@example.com';

11. Try It Yourself

Add two products. Insert two new products of your choice in a single INSERT statement, using RETURNING id, name. What ids did you get? Why are they not 9 and 10 if you had earlier failed inserts?
Cause each error. Trigger a unique violation on customers, a NOT NULL violation, and a CHECK violation on products. For each one, copy the DETAIL line and explain in your own words what it tells you.
Select first, then update. Raise the price of every product in the Books category by 10 percent. First write a SELECT showing the rows that will change, then the UPDATE (hint: price * 1.10). Check the count in the UPDATE n reply matches your SELECT.
Scratch table disaster. Make a scratch copy of customers with CREATE TABLE customers_scratch AS SELECT * FROM customers. Run a DELETE with no WHERE on it, check how many rows are left, then drop it. Why is CREATE TABLE ... AS a good way to practice?
Upsert a price list. Write an INSERT ... ON CONFLICT statement for customers that adds a new customer if the email is new, and otherwise updates only the name. Run it twice with different names and the same email. How many rows are in the table afterward?
Rebuild. Make a mess of both tables, then restore them by running seed.sql with -v ON_ERROR_STOP=1. Confirm with SELECT count(*) on each table that you are back to 5 customers and 8 products.

Where to go next. Your tables now hold real data. The most useful thing you can do with data is ask questions about it, and for that you need SELECT. The next chapter covers filtering, sorting, pattern matching, NULL surprises, and the order in which SQL actually evaluates a query: Querying with SELECT.