Topic 9 of 52
INSERT INTO
Overview
INSERT INTO adds new rows to a table. It is the primary way to load data and supports single-row, multi-row, and INSERT...SELECT patterns for bulk operations.
Syntax
sql
-- Single row insert
INSERT INTO users (name, email, role)
VALUES ('Priya Sharma', 'priya@example.com', 'admin');
-- Multi-row insert (much faster than multiple single inserts)
INSERT INTO products (name, price, category_id)
VALUES
('iPhone 16', 89999, 1),
('Samsung S25', 79999, 1),
('MacBook Pro', 199999, 2);
-- INSERT ... SELECT (copy/transform data)
INSERT INTO archived_orders (SELECT * FROM orders WHERE status = 'delivered' AND created_at < '2024-01-01');
-- RETURNING clause (PostgreSQL) — get inserted data back
INSERT INTO users (name, email)
VALUES ('Rahul', 'rahul@example.com')
RETURNING id, created_at;
-- ON CONFLICT (upsert — insert or update)
INSERT INTO user_preferences (user_id, theme)
VALUES (42, 'dark')
ON CONFLICT (user_id) DO UPDATE SET theme = EXCLUDED.theme;Common Pitfalls
- Always specify column names in INSERT — relying on column order breaks when schema changes.
- Multi-row inserts are 10-100x faster than individual inserts — batch your inserts for bulk loads.
- Interview tip: ON CONFLICT DO UPDATE (upsert) is the PostgreSQL equivalent of MySQL's INSERT ... ON DUPLICATE KEY UPDATE.
Real-World Example
Bulk loading product catalog from a staging table:
example
sql
-- Load products from CSV staging table after validation
INSERT INTO products (name, sku, category_id, price, stock, is_active)
SELECT
s.product_name,
s.sku,
c.id AS category_id,
s.price::DECIMAL(12,2),
COALESCE(s.stock_qty, 0),
TRUE
FROM staging_products s
JOIN categories c ON LOWER(s.category_name) = LOWER(c.name)
WHERE s.price > 0
AND s.sku NOT IN (SELECT sku FROM products) -- skip duplicates
ON CONFLICT (sku) DO NOTHING; -- safety: skip if SKU already exists
-- Get IDs of newly inserted rows
WITH inserted AS (
INSERT INTO orders (user_id, total_amount, status)
VALUES (101, 1299.00, 'pending')
RETURNING id, created_at
)
SELECT id, created_at FROM inserted;