PostgreSQL Zero to Advanced #3: CRUD with INSERT, SELECT, UPDATE & DELETE

Our tables from Part 2 are ready but empty. In this part we fill them with sample data and learn the four operations behind almost every feature you will ever build: Create (INSERT), Read (SELECT), Update (UPDATE) and Delete (DELETE). We will also meet RETURNING, a PostgreSQL feature that saves you a lot of extra queries.

The data you insert here is used for the rest of the series, so type it exactly as shown (or copy-paste it) to get the same outputs in later parts.

INSERT: adding rows

One row, with RETURNING

List the columns you are filling, then the values in the same order. Adding RETURNING makes the INSERT hand back the new row, which is perfect when your application needs the generated ID:

INSERT INTO users (name, email, city, joined_at)
VALUES ('Budi Santoso', 'budi@example.com', 'Jakarta', '2025-01-10')
RETURNING id, name, joined_at;

Output:

 id |     name     | joined_at  
----+--------------+------------
  1 | Budi Santoso | 2025-01-10
(1 row)

INSERT 0 1

psql shows the returned row first and then the command tag INSERT 0 1, which means "one row inserted".

Many rows at once

A single multi-row INSERT is much faster than many separate statements, because it is one round trip and one transaction:

INSERT INTO users (name, email, city, joined_at) VALUES
    ('Siti Rahma',   'siti@example.com',  'Bandung',  '2025-02-14'),
    ('Andi Wijaya',  'andi@example.com',  'Surabaya', '2025-03-01'),
    ('Dewi Lestari', 'dewi@example.com',  'Jakarta',  '2025-03-20'),
    ('Rudi Hartono', 'rudi@example.com',  'Medan',    '2025-04-05'),
    ('Rina Putri',   'rina@example.com',  'Bandung',  '2025-05-12'),
    ('Agus Salim',   'agus@example.com',  NULL,       '2025-06-01'),
    ('Maya Sari',    'maya@example.com',  'Depok',    '2025-06-18');

Output:

INSERT 0 7

Agus has NULL as his city. That is allowed because city is optional. Now the eight courses. Note the last column: React requires JavaScript Basics (ID 3), Next.js requires React (ID 4), and so on:

INSERT INTO courses (title, category, price, level, is_published, prerequisite_id) VALUES
    ('SQL Fundamentals',              'database', 150000, 'beginner',     true,  NULL),
    ('PostgreSQL Performance Tuning', 'database', 350000, 'advanced',     true,  5),
    ('JavaScript Basics',             'web',      120000, 'beginner',     true,  NULL),
    ('React for Beginners',           'web',      250000, 'intermediate', true,  3),
    ('Advanced SQL Queries',          'database', 275000, 'intermediate', true,  1),
    ('Next.js Full Stack',            'web',      400000, 'advanced',     true,  4),
    ('Python for Data Analysis',      'data',     300000, 'intermediate', true,  NULL),
    ('Docker Essentials',             'devops',   200000, 'beginner',     false, NULL);

Output:

INSERT 0 8

Finally, fourteen orders. We pass explicit timestamps so your results match the article exactly:

INSERT INTO orders (user_id, course_id, amount, status, ordered_at) VALUES
    (1, 1, 150000, 'paid',     '2025-07-01 09:15:00+00'),
    (1, 5, 275000, 'paid',     '2025-07-10 14:00:00+00'),
    (2, 3, 120000, 'paid',     '2025-07-03 10:30:00+00'),
    (2, 4, 250000, 'paid',     '2025-07-20 19:45:00+00'),
    (3, 1, 150000, 'paid',     '2025-07-05 08:00:00+00'),
    (3, 7, 300000, 'pending',  '2025-08-02 21:10:00+00'),
    (4, 3, 120000, 'paid',     '2025-08-04 11:20:00+00'),
    (4, 4, 250000, 'refunded', '2025-08-09 16:00:00+00'),
    (4, 6, 400000, 'paid',     '2025-08-15 13:30:00+00'),
    (5, 7, 300000, 'paid',     '2025-08-18 09:00:00+00'),
    (6, 1, 150000, 'paid',     '2025-09-01 10:00:00+00'),
    (6, 2, 350000, 'pending',  '2025-09-03 20:00:00+00'),
    (8, 6, 400000, 'paid',     '2025-09-10 15:45:00+00'),
    (1, 2, 350000, 'paid',     '2025-09-12 12:00:00+00');

Output:

INSERT 0 14

SELECT: reading data

SELECT * returns every column. It is great for exploring in psql:

SELECT * FROM users;

Output:

 id |     name     |      email       |   city   | joined_at  
----+--------------+------------------+----------+------------
  1 | Budi Santoso | budi@example.com | Jakarta  | 2025-01-10
  2 | Siti Rahma   | siti@example.com | Bandung  | 2025-02-14
  3 | Andi Wijaya  | andi@example.com | Surabaya | 2025-03-01
  4 | Dewi Lestari | dewi@example.com | Jakarta  | 2025-03-20
  5 | Rudi Hartono | rudi@example.com | Medan    | 2025-04-05
  6 | Rina Putri   | rina@example.com | Bandung  | 2025-05-12
  7 | Agus Salim   | agus@example.com |          | 2025-06-01
  8 | Maya Sari    | maya@example.com | Depok    | 2025-06-18
(8 rows)

In application code, list the columns you actually need. It is faster, clearer, and does not break when someone adds a column later:

SELECT id, title, category, price, level, is_published
FROM courses;

Output:

 id |             title             | category | price  |    level     | is_published 
----+-------------------------------+----------+--------+--------------+--------------
  1 | SQL Fundamentals              | database | 150000 | beginner     | t
  2 | PostgreSQL Performance Tuning | database | 350000 | advanced     | t
  3 | JavaScript Basics             | web      | 120000 | beginner     | t
  4 | React for Beginners           | web      | 250000 | intermediate | t
  5 | Advanced SQL Queries          | database | 275000 | intermediate | t
  6 | Next.js Full Stack            | web      | 400000 | advanced     | t
  7 | Python for Data Analysis      | data     | 300000 | intermediate | t
  8 | Docker Essentials             | devops   | 200000 | beginner     | f
(8 rows)

Booleans are displayed as t and f in psql. Docker Essentials is our only unpublished course (a draft).

You can filter with WHERE. We will go deep on filtering in Part 4, but here is a taste: all of Budi's orders.

SELECT * FROM orders WHERE user_id = 1;

Output:

 id | user_id | course_id | amount | status |       ordered_at       
----+---------+-----------+--------+--------+------------------------
  1 |       1 |         1 | 150000 | paid   | 2025-07-01 09:15:00+00
  2 |       1 |         5 | 275000 | paid   | 2025-07-10 14:00:00+00
 14 |       1 |         2 | 350000 | paid   | 2025-09-12 12:00:00+00
(3 rows)

A SELECT can also compute new values. AS gives the computed column a name (an alias):

SELECT title, price, price * 0.9 AS discounted_price
FROM courses
WHERE category = 'web';

Output:

        title        | price  | discounted_price 
---------------------+--------+------------------
 JavaScript Basics   | 120000 |         108000.0
 React for Beginners | 250000 |         225000.0
 Next.js Full Stack  | 400000 |         360000.0
(3 rows)

Multiplying an integer by 0.9 produces a NUMERIC, which is why you see .0. Nothing in the table changed; SELECT never modifies data.

UPDATE: changing rows

Maya has moved from Depok to Jakarta. UPDATE sets new values for the rows matched by WHERE:

UPDATE users
SET city = 'Jakarta'
WHERE email = 'maya@example.com'
RETURNING id, name, city;

Output:

 id |   name    |  city   
----+-----------+---------
  8 | Maya Sari | Jakarta
(1 row)

UPDATE 1

RETURNING works with UPDATE too, so you can confirm exactly what changed. UPDATE 1 tells you one row was affected. You can update several columns in one statement by separating them with commas:

UPDATE courses
SET title = 'Docker Essentials for Developers',
    level = 'beginner'
WHERE id = 8
RETURNING id, title, level, is_published;

Output:

 id |              title               |  level   | is_published 
----+----------------------------------+----------+--------------
  8 | Docker Essentials for Developers | beginner | f
(1 row)

UPDATE 1

Always match rows by something unique, such as the primary key or the email. If your WHERE matches more rows than you expected, they will all be changed.

DELETE: removing rows

Let's create a temporary test account and then delete it:

INSERT INTO users (name, email, city)
VALUES ('Test User', 'test@example.com', 'Bogor')
RETURNING id, name;

Output:

 id |   name    
----+-----------
  9 | Test User
(1 row)

INSERT 0 1

DELETE FROM users
WHERE email = 'test@example.com'
RETURNING *;

Output:

 id |   name    |      email       | city  | joined_at  
----+-----------+------------------+-------+------------
  9 | Test User | test@example.com | Bogor | 2026-10-13
(1 row)

DELETE 1

DELETE ... RETURNING * gives you the deleted row, which is handy for audit logs or an "undo" feature. Since we did not pass joined_at, it had defaulted to the current date.

Here is our final list of users. Note that the next new user will get ID 10, not 9, because the sequence already handed out 9:

SELECT id, name, city FROM users;

Output:

 id |     name     |   city   
----+--------------+----------
  1 | Budi Santoso | Jakarta
  2 | Siti Rahma   | Bandung
  3 | Andi Wijaya  | Surabaya
  4 | Dewi Lestari | Jakarta
  5 | Rudi Hartono | Medan
  6 | Rina Putri   | Bandung
  7 | Agus Salim   | 
  8 | Maya Sari    | Jakarta
(8 rows)

Safety net: preview with a transaction

Before running a risky UPDATE or DELETE on important data, wrap it in a transaction. You can look at the result and undo everything with ROLLBACK:

BEGIN;
DELETE FROM orders WHERE status = 'pending';
-- check the result, e.g. SELECT COUNT(*) FROM orders;
ROLLBACK;  -- nothing was actually deleted

We will explore transactions properly in Part 9. For now, just remember that BEGIN ... ROLLBACK is your friend.

Common mistakes

• UPDATE or DELETE without WHERE. UPDATE users SET city = 'Jakarta'; changes every user. Write the WHERE clause first, or run a SELECT with the same WHERE to preview.
• Not listing columns in INSERT. INSERT INTO users VALUES (...) depends on column order and breaks as soon as the table changes.
• Inserting a value into an identity column. With GENERATED ALWAYS, PostgreSQL rejects manual IDs unless you use OVERRIDING SYSTEM VALUE. Let the database generate them.
• Running a second SELECT just to get the new ID. Use RETURNING id instead; it is atomic and saves a round trip.
• Using double quotes for strings. WHERE name = "Budi" looks for a column named Budi. Use single quotes.

Exercise

1. Insert a new user "Sari Wulandari" (sari@example.com, Semarang) and return her ID. Which ID did she get, and why?
2. Update her city to Solo and return name and city.
3. Select title and price of all courses, plus a column price_with_tax that adds 11%.
4. Delete Sari again with RETURNING *, so your data stays in sync with the next parts.

Summary

You can now create, read, update and delete rows, insert many rows in one statement, compute values in a SELECT, and use RETURNING to get results back from write operations. Our course platform now holds 8 users, 8 courses and 14 orders.

In Part 4 we will query this data with precision: filtering with WHERE, LIKE/ILIKE, IN and BETWEEN, sorting with ORDER BY, and building pagination with LIMIT/OFFSET.