Learn SQL with PostgreSQL - Data Manipulation Language (DML) & Basic Querying

#Introduction

Welcome to episode 4 of the Learn SQL with PostgreSQL series! In episode 3, we built a strict table structure with the right data types and constraints. Now it's time to give that structure "life": filling it with data and manipulating it. This is the part of SQL you'll use most often in your day-to-day work as a developer.

The language we learn today is called DML (Data Manipulation Language). Unlike DDL, which manages the database structure, DML manages the data inside it: INSERT to add new rows, UPDATE to modify existing rows, and DELETE to remove rows. Together with SELECT, which we'll explore in depth in episode 5, these four commands make up 90% of everyday SQL work.

One thing that makes this episode special is a signature PostgreSQL feature: the RETURNING clause. With this feature, you can directly retrieve data that was just inserted, updated, or deleted without having to run an additional SELECT query. This small feature saves a lot of round-trips and is often what sets PostgreSQL apart from other databases.

#INSERT: Inserting Data

The INSERT INTO command is the standard way to add rows to a table. PostgreSQL supports several patterns you need to master.

#Single Row Insert

INSERT INTO users (email, full_name, age)
VALUES ('budi@example.com', 'Budi Santoso', 25);

Notice that the id and created_at columns don't need to be filled because they have defaults (identity and now()). The column list before VALUES determines the value mapping — a good habit that keeps queries clear even when the table structure changes.

#Multi-Row Insert

PostgreSQL lets you insert many rows at once in a single command — far more efficient than running many separate inserts:

INSERT INTO users (email, full_name, age)
VALUES
    ('sari@example.com', 'Sari Wulandari', 30),
    ('andi@example.com', 'Andi Pratama', 22),
    ('dewi@example.com', 'Dewi Lestari', 28);

One command, three rows. If even a single row fails (e.g. it violates a constraint), the whole batch fails too — this is consistent with the atomicity principle we'll discuss in episode 11.

#INSERT INTO ... SELECT

A very powerful pattern: taking data from another query to insert. This is often used for archiving, migration, or seeding data:

INSERT INTO active_users (email, full_name)
SELECT email, full_name
FROM users
WHERE is_active = TRUE;

The query above copies all active users into the active_users table. This is also useful for reading data from a CSV file via the \copy command in psql.

#UPDATE: Modifying Data

UPDATE changes the values of columns on the rows that match a condition. Without a WHERE clause, every row in the table changes — so always make sure your WHERE condition is correct.

UPDATE users
SET age = 26
WHERE email = 'budi@example.com';

Multiple columns can be changed at once, and we can use values from expressions:

UPDATE products
SET price = price * 1.10,
    updated_at = now()
WHERE category = 'elektronik';

Notice the price = price * 1.10 pattern — a new value can be computed from the old value of that same column. This is very common for price increases, stock additions, or other arithmetic operations.

#DELETE: Removing Data

DELETE removes the rows that match a WHERE condition. If the WHERE clause is omitted, all rows are deleted — in episode 2 we learned that TRUNCATE is far faster for that purpose.

DELETE FROM users
WHERE email = 'budi@example.com';

DELETE FROM orders
WHERE user_id IN (
    SELECT id FROM users WHERE is_active = FALSE
);

The second example deletes all orders from users who are no longer active. The IN pattern with a subquery will be explored in depth in episode 7.

#PostgreSQL's Signature Feature: RETURNING

This is the PostgreSQL advantage that developers love. The RETURNING clause returns the rows that were just manipulated as the query result — so no additional SELECT is needed to know what happened.

#RETURNING on INSERT

Imagine you insert a user and need its id to continue the process (e.g. creating a session). In many databases, you'd have to run a second query. In PostgreSQL, one is enough:

INSERT INTO users (email, full_name, age)
VALUES ('rudi@example.com', 'Rudi Hartono', 27)
RETURNING id, created_at;

The result is directly a single row containing the id and created_at of the data that was just inserted. Without RETURNING, you'd have to guess the id or run another query.

#RETURNING on UPDATE and DELETE

RETURNING also works on UPDATE and DELETE — very useful for auditing or logging:

UPDATE products
SET stock = stock - 2
WHERE id = '1c2d3e4f-0000-0000-0000-000000000001'
RETURNING id, name, stock;

DELETE FROM users
WHERE email = 'dewi@example.com'
RETURNING id, email;

On UPDATE, RETURNING returns the rows after the change is applied. On DELETE, it returns the rows that were deleted — valuable information for logging or archiving tables.

#Combining with ON CONFLICT

In the real world, INSERT often collides with UNIQUE constraints. PostgreSQL provides ON CONFLICT to handle these collisions gracefully — updating the existing data instead of failing:

INSERT INTO users (email, full_name, age)
VALUES ('sari@example.com', 'Sari Wulandari', 31)
ON CONFLICT (email)
DO UPDATE SET age = EXCLUDED.age
RETURNING id, email, age;

EXCLUDED refers to the row that failed to be inserted. So if the email sari@example.com already exists, its age is updated to 31. This kind of "upsert" pattern is very common in production, for example syncing data from an external API.

#Common Mistakes When Using DML

Another one that often happens: forgetting to add RETURNING when you need the result of a manipulation. Since we can't always predict GENERATED ALWAYS id values, running a separate SELECT is wasteful and prone to race conditions. Get into the habit of always thinking "do I need the result?" before executing.

#Closing

In this episode 4 we mastered DML: inserting data with INSERT (single, multi-row, and from a SELECT), modifying data with UPDATE, deleting with DELETE, and using PostgreSQL's signature RETURNING feature to retrieve manipulation results directly. We also learned to handle constraint conflicts with ON CONFLICT.

Key takeaways:

• INSERT supports single, multi-row, and INSERT ... SELECT.
• Always write WHERE before UPDATE and DELETE — it's a life-saving habit.
• RETURNING returns the manipulated rows without an extra query.
• ON CONFLICT handles constraint collisions with an upsert pattern.
• DML is atomic: one failure fails the entire batch.

In episode 5, we'll get into the commands you'll execute most often in your life as a developer: Filtering, Sorting, Paging & Aggregation Queries — from SELECT, WHERE with comparison operators, LIKE/ILIKE, ORDER BY, LIMIT and OFFSET, to aggregation with GROUP BY and HAVING. Your data is ready; now it's time to turn it into information.