Warming up the neural circuits...
An empty table is useless. INSERT is how you put data into your database. By the end of this module you will:
RETURNINGON CONFLICTWhat it is: INSERT INTO is a DML (Data Manipulation Language) command that adds new rows to a table. You specify which columns to fill and provide the corresponding values.
Why we use it: Without INSERT, there's no way to add data to your database. Every user registration, product creation, or order placement runs an INSERT statement behind the scenes.
When we use it: Every time new data needs to be stored — user signups, submissions, calls that create resources, data imports, etc.
INSERT INTO users (username, email, age)
VALUES ('alice_dev', 'alice@example.com', 25);Always list your columns. Writing INSERT INTO users VALUES (...) is dangerous — if you add a new column to the table later, this command will break.
What it is: Multi-row INSERT allows you to insert multiple rows in a single statement by providing multiple value tuples. All rows are inserted atomically — either all succeed or all fail.
Why we use it: Running 100 separate INSERT statements is slow because each one requires a round-trip to the database. Multi-row INSERT sends all data in one command, reducing network overhead and improving performance by 10-100x.
When we use it: During data imports, seeding test data, bulk operations, or any time you need to insert more than one row.
INSERT INTO users (username, email, age)
VALUES
('bob_codes', 'bob@example.com', 30),
('charlie_writes', 'charlie@example.com', 22),
('diana_dev', 'diana@example.com', 28);What it is: RETURNING is a PostgreSQL extension to INSERT (and UPDATE/DELETE) that returns the inserted row's data immediately — including auto-generated columns like id and created_at.
Why we use it: In modern applications, after creating a resource (like a user), you need to return it to the client with its generated ID. Without RETURNING, you'd need two queries: INSERT then SELECT. RETURNING does it in one.
When we use it: In APIs after creating resources, when you need the auto-generated ID, or when building operations that need to verify what was inserted.
-- Get back the auto-generated id and timestamp
INSERT INTO users (username, email)
VALUES ('new_user', 'new@example.com')
RETURNING id, created_at;
-- Get back ALL columns
INSERT INTO users (username, email)
VALUES ('another_user', 'another@example.com')
RETURNING *;Why RETURNING matters: In a REST API, after creating a resource, you return it to the client. RETURNING lets you do this in one query instead of INSERT + SELECT.
What it is: Instead of providing literal values, you can use expressions in the VALUES clause — string concatenation, function calls, arithmetic, and more. PostgreSQL evaluates these expressions during insertion.
Why we use it: Sometimes you need to compute values before storing them — generating UUIDs, concatenating names, applying transformations, or using database functions.
When we use it: When inserting computed data, generating unique identifiers, or transforming data during import.
-- Insert with a computed full name
INSERT INTO users (username, email, full_name)
VALUES ('john_doe', 'john@example.com', 'John' || ' ' || 'Doe');
-- Insert with a UUID
INSERT INTO sessions (id, user_id)
VALUES (gen_random_uuid(), 1);What it is: Instead of providing literal values, you can insert the results of a SELECT query. This copies data from one table (or query) into another.
Why we use it: When migrating data between tables, creating backups, populating archive tables, or copying filtered data from one structure to another.
When we use it: During data migrations, creating summary/aggregate tables, populating test data from production, or building materialized views.
-- Copy all published posts into an archive table
INSERT INTO posts_archive (title, content, user_id)
SELECT title, content, user_id
FROM posts
WHERE published = true;What it is: ON CONFLICT is PostgreSQL's "upsert" — it handles duplicate key conflicts by either doing nothing (DO NOTHING) or updating the existing row (DO UPDATE SET). It's a single atomic operation.
Why we use it: In real applications, you often need to "insert if not exists, update if exists" — for example, updating a user's last login time, syncing external data, or importing data that may contain duplicates.
When we use it: During data imports with potential duplicates, caching layers, session management, or any idempotent operation that should be safe to run multiple times.
-- Insert a user, or update their email if username already exists
INSERT INTO users (username, email)
VALUES ('alice_dev', 'alice_new@example.com')
ON CONFLICT (username)
DO UPDATE SET email = EXCLUDED.email;
-- Do nothing on conflict (ignore duplicates)
INSERT INTO users (username, email)
VALUES ('alice_dev', 'alice@example.com
ON CONFLICT is PostgreSQL's "upsert" — a powerful pattern for data imports, caching, and idempotent operations.
INSERT INTO table VALUES (...) breaks when the schema changesUsing your courses table:
('SQL Basics', 'John Doe', 2999, true)RETURNING id to see the generated IDsON CONFLICT DO NOTHINGRETURNING *Next up: Data is in the table — now let's get it back out. In the next module, you'll master SELECT — the most common SQL command you'll ever run.