INSERT, Generated Values & Bulk Loading — SQL Course

What you'll learn

Insert single and multiple rows, retrieve generated values, handle conflicts deliberately, and load data in bulk. The lab uses PostgreSQL while identifying the semantics that transfer to other relational systems.

By the end of this lesson, you'll be able to:

Apply INSERT VALUES to a realistic data question
Apply Column lists to a realistic data question
Apply RETURNING to a realistic data question
Apply INSERT SELECT to a realistic data question

Core mental model

SQL is declarative: describe the result or invariant you need, then let the database choose a physical execution strategy. Use this table to connect syntax to design decisions.

Concept	What it means	Decision rule
Column list	The explicit target columns for inserted values	Always include it so schema order changes cannot corrupt meaning
RETURNING	Rows produced by a data-changing statement	Use to obtain database-generated identity and defaults
Conflict action	Atomic behavior for a uniqueness collision	Choose DO NOTHING or a narrowly scoped DO UPDATE from business semantics

Professional workflow

Work from a defined question and result grain, then verify correctness before performance.

State the insert boundary question and the exact grain of the expected result.
Inspect table definitions, keys, constraints, representative values, and row counts.
Write the smallest correct query with explicit columns, aliases, and predicates.
Test missing, duplicate, boundary, and NULL cases before trusting the result.
Inspect the execution plan or affected rows when cost or data change matters.
Save the query with its assumptions, parameters, verification, and recovery notes.

Make results explainable

Keep each query in a saved SQL file with a short statement of its purpose, expected grain, assumptions, and verification query.

Guided SQL lab

Insert or update a product safely

The unique SKU is the conflict target and RETURNING provides the authoritative stored row.

upsert_product.sql

INSERT INTO inventory.products (sku, name, price)
VALUES ('SQL-001', 'SQL Field Guide', 32.00)
ON CONFLICT (sku) DO UPDATE
SET name = EXCLUDED.name,
    price = EXCLUDED.price
RETURNING id, sku, name, price, created_at;

Production practice

Contract

Define the expected row grain, inputs, output columns, invariants, and failure or empty-result behavior before writing SQL.

Verification

Use representative fixtures and independent row-count, uniqueness, NULL, and boundary checks; compare plans when cost matters.

Operations

Save reviewed SQL with explicit schema names where appropriate, bounded scope, least privilege, observability, and a recovery path for changes.

Common failure mode

A broad upsert can overwrite newer state. Update only fields whose conflict semantics are explicitly defined.

Independent workshop

Build a review-ready insert boundary lab against the course commerce dataset.

Your finished workshop must include:

INSERT VALUES
Column lists
RETURNING
INSERT SELECT
ON CONFLICT
Verification notes and edge-case evidence

Definition of done

Run the expected case and at least two edge cases, verify row counts and grain, and add comments explaining any vendor-specific behavior.

Recap & quick check

Key takeaways

Column list: Always include it so schema order changes cannot corrupt meaning
RETURNING: Use to obtain database-generated identity and defaults
Conflict action: Choose DO NOTHING or a narrowly scoped DO UPDATE from business semantics

Quick check

1. Which rule best applies to Column list?

2. Which rule best applies to RETURNING?

3. Which rule best applies to Conflict action?

Next: UPDATE & Safe State Changes