INSERT INTO

Learning Focus

Use this lesson to understand INSERT INTO with practical syntax and examples.

Concept Overview

Definition

INSERT INTO adds new rows to a PostgreSQL table. It is the primary way to persist new data from applications (forms, APIs, ETL jobs) into durable storage.

PostgreSQL inserts are commonly paired with:

• constraints (to prevent invalid writes)
• transactions (to keep multi-step writes consistent)
• RETURNING (to verify exactly what was inserted)
• ON CONFLICT (upsert and idempotent write patterns)

Why is it important?

• Data creation: without inserts, tables stay empty
• Correctness: constraints + inserts enforce business rules at the data boundary
• Performance: multi-row inserts and bulk loading strategies affect throughput
• Reliability: transactions and RETURNING reduce partial writes and verification gaps

Where does it fit?

INSERT INTO is part of DML (Data Manipulation Language), alongside SELECT, UPDATE, and DELETE. In CRUD terms, it is the Create operation.

---

Syntax & Rules

Core Syntax

-- Insert a single row (recommended: explicit column list)
INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...);

-- Insert multiple rows
INSERT INTO table_name (column1, column2, ...)
VALUES
  (value1a, value2a, ...),
  (value1b, value2b, ...);

-- Insert from another query
INSERT INTO target_table (col1, col2, ...)
SELECT expr1, expr2, ...
FROM source_table
WHERE condition;

-- Return inserted rows
INSERT INTO table_name (...) VALUES (...)
RETURNING *;

-- Conflict handling (PostgreSQL upsert)
INSERT INTO table_name (...) VALUES (...)
ON CONFLICT (unique_key)
DO UPDATE SET col = EXCLUDED.col;

Available Options / Parameters

Feature	PostgreSQL form	Typical use
Insert defaults	DEFAULT VALUES	create row with defaults
Return inserted values	RETURNING	get generated IDs, verify writes
Ignore duplicates	ON CONFLICT DO NOTHING	idempotent inserts
Upsert	ON CONFLICT DO UPDATE	insert-or-update on unique key
Bulk load	COPY (server-side) / \copy (psql)	high-volume imports

Key Rules and Considerations

• Prefer explicit column lists to avoid breakage when schemas change.
• Values must match target column types (casts can help, but avoid relying on implicit casts).
• NOT NULL / CHECK / FK constraints are enforced during insert.
• ON CONFLICT (...) requires a matching unique or exclusion constraint.
• Too many indexes can slow inserts; index only what queries need.

---

Step-by-Step Examples

Example 1: Single-Row Insert + Verify (Beginner)

-- Create a sample table
CREATE TABLE employees (
  employee_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  first_name  text NOT NULL,
  last_name   text NOT NULL,
  hire_date   date NOT NULL,
  salary      numeric(12,2) NOT NULL CHECK (salary >= 0)
);

-- Insert one row
INSERT INTO employees (first_name, last_name, hire_date, salary)
VALUES ('Ana', 'Ng', '2025-01-10', 68000.00);

-- Verify
SELECT employee_id, first_name, last_name, hire_date, salary
FROM employees
ORDER BY employee_id;

Expected output:

 employee_id | first_name | last_name | hire_date   | salary
-------------+------------+-----------+-------------+----------
 1           | Ana        | Ng        | 2025-01-10  | 68000.00
(1 row)

Explanation:

• Identity primary key generates employee_id automatically.
• Explicit columns protect you from schema reordering.

---

Example 2: Multi-Row Insert (Intermediate)

-- Insert multiple rows
INSERT INTO employees (first_name, last_name, hire_date, salary)
VALUES
  ('Bilal', 'Singh', '2025-02-01', 72000.00),
  ('Chloe', 'Fernandez', '2025-02-15', 70000.00);

-- Verify row count
SELECT COUNT(*) AS employee_count
FROM employees;

Expected output:

 employee_count
----------------
 3
(1 row)

Explanation:

• Multi-row inserts are faster than many single-row inserts.
• Use transactions for large batches.

---

Example 3: Insert Only Some Columns (Intermediate)

Add a column with a default, then insert without specifying it.

ALTER TABLE employees ADD COLUMN active boolean NOT NULL DEFAULT true;

-- Insert with omitted column (active uses default true)
INSERT INTO employees (first_name, last_name, hire_date, salary)
VALUES ('Dara', 'Jones', '2025-03-01', 61000.00)
RETURNING employee_id, first_name, active;

Expected output:

 employee_id | first_name | active
-------------+------------+--------
 4           | Dara       | t
(1 row)

Explanation:

• Defaults and NOT NULL constraints work together to prevent missing data.
• RETURNING lets you verify inserts without a second query.

---

Example 4: Insert From Another Table (Advanced)

Staging-table pattern: load raw rows, then insert validated rows.

-- Staging table
CREATE TABLE new_hires (
  nh_id      bigint,
  fname      text,
  lname      text,
  start_date date,
  base_pay   numeric(12,2),
  valid      boolean NOT NULL DEFAULT false
);

INSERT INTO new_hires (nh_id, fname, lname, start_date, base_pay, valid) VALUES
(10, 'Evan', 'Lee', '2025-03-10', 65000.00, true),
(11, 'Fatima', 'Rahman', '2025-03-12', 64000.00, true),
(12, 'Bad', NULL, '2025-03-12', 0.00, false);

-- Insert only valid rows into employees
INSERT INTO employees (first_name, last_name, hire_date, salary)
SELECT fname, lname, start_date, base_pay
FROM new_hires
WHERE valid = true
RETURNING employee_id, first_name, last_name;

Expected output:

 employee_id | first_name | last_name
-------------+------------+----------
 5           | Evan       | Lee
 6           | Fatima     | Rahman
(2 rows)

Explanation:

• INSERT ... SELECT is a core ETL building block.
• Filter invalid rows before inserting into the primary table.

---

Example 5: Upsert With ON CONFLICT (Advanced)

PostgreSQL upserts replace MySQL's ON DUPLICATE KEY UPDATE.

-- Ensure a unique key exists
ALTER TABLE employees ADD CONSTRAINT employees_name_unique UNIQUE (first_name, last_name);

-- Insert a row; if it exists, update salary
INSERT INTO employees (first_name, last_name, hire_date, salary)
VALUES ('Chloe', 'Fernandez', '2025-02-15', 73000.00)
ON CONFLICT (first_name, last_name)
DO UPDATE SET
  salary = EXCLUDED.salary
RETURNING employee_id, first_name, last_name, salary;

Expected output:

 employee_id | first_name | last_name  | salary
-------------+------------+------------+----------
 3           | Chloe      | Fernandez  | 73000.00
(1 row)

Explanation:

• EXCLUDED refers to the proposed insert row.
• The conflict target must match a unique/exclusion constraint.

---

Practical Use Cases

1. New Product Launch (E-commerce)

Business problem: Add new SKUs to a catalog.

INSERT INTO products (sku, name, price_cents, active)
VALUES ('SKU-1001', 'Laptop', 99999, true);

2. User Signup (SaaS)

Business problem: Create a user and return the generated ID.

INSERT INTO users (email, created_at)
VALUES ('dev@example.com', now())
RETURNING user_id;

3. Idempotent Event Ingestion (IoT)

Business problem: Avoid duplicate device events on retries.

INSERT INTO device_events (device_id, event_id, payload)
VALUES (42, 'evt_abc', '{"temp": 31}'::jsonb)
ON CONFLICT (device_id, event_id)
DO NOTHING;

4. Audit Trail (Security)

Business problem: Record a login event.

INSERT INTO auth_logins (user_id, ip_address, logged_in_at)
VALUES (123, '203.0.113.10', now());

5. Data Migration (ETL)

Business problem: Transform and load data from a legacy table.

INSERT INTO customers (email, created_at)
SELECT lower(trim(legacy_email)), created_at
FROM legacy_customers
WHERE legacy_email IS NOT NULL;

---

Common Mistakes & Troubleshooting

Mistake 1: Mismatched column and value counts

Wrong SQL:

INSERT INTO employees (first_name, last_name)
VALUES ('Gus', 'Novak', 'EXTRA');

Error message: ERROR: INSERT has more expressions than target columns

Fix:

INSERT INTO employees (first_name, last_name, hire_date, salary)
VALUES ('Gus', 'Novak', '2025-04-01', 60000.00);

Mistake 2: Forgetting quotes for strings/dates

Wrong SQL:

INSERT INTO employees (first_name, last_name, hire_date, salary)
VALUES (Gus, Novak, 2025-04-01, 60000.00);

Error message (typical): ERROR: column "gus" does not exist

Fix:

INSERT INTO employees (first_name, last_name, hire_date, salary)
VALUES ('Gus', 'Novak', '2025-04-01', 60000.00);

Mistake 3: Unique constraint violation

Wrong SQL:

INSERT INTO employees (first_name, last_name, hire_date, salary)
VALUES ('Ana', 'Ng', '2025-01-10', 68000.00);

Error message: ERROR: duplicate key value violates unique constraint "employees_pkey" (or another UNIQUE constraint)

Fix: use ON CONFLICT DO NOTHING or ON CONFLICT DO UPDATE.

INSERT INTO employees (first_name, last_name, hire_date, salary)
VALUES ('Ana', 'Ng', '2025-01-10', 68000.00)
ON CONFLICT (first_name, last_name)
DO NOTHING;

Mistake 4: ON CONFLICT without a matching constraint

Wrong SQL:

INSERT INTO employees (first_name, last_name, hire_date, salary)
VALUES ('Zoe', 'Zed', '2025-05-01', 70000.00)
ON CONFLICT (salary)
DO NOTHING;

Error message: ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification

Fix: define a unique constraint/index on the conflict target, or change the conflict target.

Debugging Tips

1. Confirm column names and constraints with \d employees in psql.
2. Use RETURNING to verify inserted rows.
3. Wrap bulk inserts in a transaction and ROLLBACK during testing.
4. If inserts are slow, check index count and triggers; measure with EXPLAIN for related read queries.

---

Best Practices

✅ Always specify a column list in INSERT statements.
✅ Always use RETURNING when you need generated IDs or want to verify writes.
✅ Always use transactions for multi-step inserts and batch operations.
✅ Always design constraints first (NOT NULL, CHECK, UNIQUE) to prevent bad data.
✅ Always use ON CONFLICT for idempotent writes and upsert logic.
❌ Avoid inserting into every column by position; schema changes will break you.
❌ Avoid using INSERT as a bulk loader for very large datasets; prefer COPY.
❌ Avoid adding unnecessary indexes; each index adds write overhead.

---

Hands-On Practice

Exercise 1 (Easy)

Task: Insert one employee named "Evan Lee" with salary 62000 and hire date 2025-06-01.

-- Write your query here

Solution:

INSERT INTO employees (first_name, last_name, hire_date, salary)
VALUES ('Evan', 'Lee', '2025-06-01', 62000.00);

Exercise 2 (Medium)

Task: Insert three employees in one statement and then return their generated IDs.

-- Write your query here

Solution:

INSERT INTO employees (first_name, last_name, hire_date, salary)
VALUES
  ('Hana', 'Ito', '2025-06-10', 61000.00),
  ('Ivan', 'Petrov', '2025-06-11', 64000.00),
  ('Jules', 'Martin', '2025-06-12', 63000.00)
RETURNING employee_id;

Exercise 3 (Advanced)

Task: Create a unique constraint on (first_name, last_name) and implement an upsert that updates salary if the row already exists.

-- Write your query here

Solution:

ALTER TABLE employees ADD CONSTRAINT employees_name_uq UNIQUE (first_name, last_name);

INSERT INTO employees (first_name, last_name, hire_date, salary)
VALUES ('Ana', 'Ng', '2025-01-10', 69000.00)
ON CONFLICT (first_name, last_name)
DO UPDATE SET salary = EXCLUDED.salary
RETURNING employee_id, salary;

---

Connection to Other Concepts

Related lesson	Why it matters
CREATE TABLE	Tables and constraints must exist before inserts
Constraints	Constraints protect data quality during inserts
UPDATE	Upserts use UPDATE semantics via DO UPDATE
Transactions	Transactions keep multi-step inserts consistent
Triggers	Triggers can add implicit insert-time behavior

---

Visual Learning Diagram

---

Common Pitfalls

Pitfall	Consequence	Prevention
Omitting column list	Inserts break after schema changes	Always specify columns explicitly
Missing constraints	Invalid data enters the system	Add NOT NULL/CHECK/UNIQUE/FK constraints
Upsert without a unique constraint	ON CONFLICT fails	Create a matching UNIQUE constraint/index
Too many indexes on a hot table	Slow inserts and updates	Index only what your queries need
Not verifying inserted rows	Silent mistakes	Use RETURNING or SELECT to verify

---

Quick Reference

INSERT INTO t (a, b) VALUES (1, 2);
INSERT INTO t (a, b) VALUES (1, 2) RETURNING *;
INSERT INTO t (a, b) VALUES (1, 2), (3, 4);
INSERT INTO t (a, b) SELECT a, b FROM s WHERE ok = true;
INSERT INTO t (k, v) VALUES (1, 'x') ON CONFLICT (k) DO UPDATE SET v = EXCLUDED.v;

What's Next

• Previous: ORDER BY - Review the previous lesson to reinforce context.
• Next: UPDATE - Continue to the next concept with incremental complexity.
• Module Overview - Return to this module index and choose another related lesson.