INSERT Queries

Introduction

INSERT adds new rows to a table. It is the most common write operation — every signup, order, like and message is an INSERT under the hood.

Beginner analogy: INSERT is writing a new row in a spreadsheet — except the database checks all your rules first.

Understanding the topic

Core concepts to understand:

• INSERT INTO users(email, name) VALUES('a@b.com','Ana');
• Bulk insert: VALUES (...), (...), (...).
• RETURNING * in Postgres returns the inserted row (great for IDs).
• INSERT ... ON CONFLICT handles duplicates (Postgres) — ON DUPLICATE KEY in MySQL.
• Always use parameterized queries from your app — never concatenate strings.

Syntax reference

Visual workflow / architecture:

App ── INSERT INTO orders(...) VALUES(...) RETURNING id ──▶ DB
                                                            │
                                                            ▼
                                                ┌────────────────┐
                                                │ Constraint     │
                                                │ checks (NN, FK)│
                                                ├────────────────┤
                                                │ WAL write      │
                                                ├────────────────┤
                                                │ Row inserted   │
                                                └────────────────┘
                       ◀────────── new id ──────────────────

INSERT INTO <table> (<col1>, <col2>, ...)
VALUES (<val1>, <val2>, ...)
[, (<val1>, <val2>, ...)]   -- optional bulk rows
[RETURNING <columns>];      -- Postgres only

INSERT INTO users (email, name, plan)
VALUES ('ana@acme.io', 'Ana', 'pro')
RETURNING id, created_at;

Real-world use

Every Stripe payment, GitHub commit comment and Uber ride event is one or more INSERTs wrapped in a transaction.

Best practices

• Always use parameterized queries — prevents SQL injection.
• Bulk-insert in batches of 100–1000 for speed.
• Use RETURNING to avoid a follow-up SELECT.

Common mistakes

• String-concat user input into INSERT — classic SQL injection.
• Inserting one-by-one in a loop — 100× slower than batch.
• Forgetting ON CONFLICT and crashing on duplicates.

Hands-on exercise

Interview preparation — practice these questions:

• Q1. Syntax to insert one row.
• Q2. How to insert many rows at once.
• Q3. What does RETURNING do?
• Q4. ON CONFLICT vs ON DUPLICATE KEY.
• Q5. Why parameterize all queries?