INSERT, UPDATE, and DELETE

INSERT

INSERT adds new rows to a table. Understanding its variants — single rows, batch inserts, inserts from queries, and upserts — lets you load data efficiently and handle conflict scenarios cleanly without extra round-trips to the database.

Single Row

-- Column names listed explicitly — safer than positional values
INSERT INTO products (name, price, stock)
VALUES ('Mechanical Keyboard', 129.99, 50);

You don’t need to list columns if you supply values for every column in order, but naming them explicitly is safer and more readable — it protects against schema changes reordering columns.

Multiple Rows

Batch inserts are significantly faster than individual inserts because they reduce round-trips to the database. For loading seed data, test fixtures, or bulk imports, always prefer a single multi-row INSERT over a loop of single-row INSERTs.

-- One statement, four rows — much faster than four separate INSERT calls
INSERT INTO products (name, price, stock)
VALUES
    ('Wireless Mouse',   45.00, 120),
    ('USB-C Hub',        79.99,  35),
    ('Monitor Stand',    59.99,  18),
    ('Laptop Sleeve',    24.99,  80);

INSERT … SELECT

INSERT ... SELECT copies rows from a query result directly into a table without materializing them in application code. This is useful for archiving, populating summary tables, or seeding data from existing rows.

-- Archive orders older than one year without moving data through the application
INSERT INTO orders_archive
SELECT * FROM orders
WHERE created_at < NOW() - INTERVAL '1 year';

-- Populate a summary table from raw data
INSERT INTO product_stats (product_id, total_sold)
SELECT product_id, SUM(quantity)
FROM order_items
GROUP BY product_id;

RETURNING

RETURNING gives you back column values from the rows you just inserted — without a separate SELECT. This is especially useful when you need the auto-generated primary key to immediately insert related rows, avoiding an extra round-trip that would otherwise be necessary.

-- Retrieve the auto-generated id immediately after insert
INSERT INTO customers (name, email)
VALUES ('Alice Nakamura', '[email protected]')
RETURNING id;

-- Return multiple columns — useful for confirming defaults were applied
INSERT INTO orders (customer_id, total_amount)
VALUES (42, 199.99)
RETURNING id, created_at;

UPDATE

UPDATE modifies existing rows that match a WHERE condition. It’s one of the most impactful operations in SQL — a mistake can silently corrupt large amounts of data, so always verify the target rows with a SELECT before running an UPDATE in production.

Basic Update

UPDATE products
SET price = 109.99
WHERE id = 1;

Update Multiple Columns

-- Multiple columns updated atomically in the same statement
UPDATE products
SET price = 109.99,
    stock = stock - 1,   -- expressions work, not just literal values
    updated_at = NOW()
WHERE id = 1;

Update With a Subquery

When the set of rows to update is defined by data in another table, use a subquery in the WHERE clause to express that relationship without duplicating logic.

-- Apply a 10% discount to all products in the "Clearance" category
UPDATE products
SET price = price * 0.90
WHERE category_id IN (
    SELECT id FROM categories WHERE name = 'Clearance'
);

UPDATE … RETURNING

Like INSERT, RETURNING on UPDATE gives you the new values immediately — no follow-up SELECT needed. This is handy for confirming what changed or chaining the result into application logic.

UPDATE orders
SET status = 'shipped', shipped_at = NOW()
WHERE id = 1001
RETURNING id, status, shipped_at;

The Danger: UPDATE Without WHERE

An UPDATE without a WHERE clause modifies every row in the table. PostgreSQL won’t ask for confirmation — it will silently update everything. Before running any UPDATE in production, run the equivalent SELECT first to confirm you’re targeting the right rows.

-- DANGEROUS: sets every product's price to 0
UPDATE products SET price = 0;

-- Safe habit: run the SELECT version first to verify the target
SELECT * FROM products WHERE category_id = 5;
-- Then: UPDATE products SET price = price * 0.90 WHERE category_id = 5;

DELETE

DELETE removes rows from a table. Like UPDATE, it operates on all rows that match the WHERE clause — and without a WHERE clause, it removes everything. The same “select before delete” habit applies.

-- Delete a specific row by primary key
DELETE FROM products WHERE id = 7;

-- Delete rows matching a condition — expired sessions
DELETE FROM sessions WHERE expires_at < NOW();

-- Delete rows based on data in another table
DELETE FROM order_items
WHERE order_id IN (
    SELECT id FROM orders WHERE status = 'cancelled'
);

DELETE … RETURNING

RETURNING works on DELETE too — useful for capturing what was removed for logging or returning deleted records to the caller.

DELETE FROM notifications
WHERE user_id = 42 AND read = TRUE
RETURNING id, message;

The Same Warning Applies

-- DANGEROUS: empties the entire table
DELETE FROM products;

Use TRUNCATE if you intentionally want to clear a whole table — it’s faster for large tables and makes the intent explicit.

TRUNCATE

TRUNCATE clears all rows instantly without scanning them one by one, making it orders of magnitude faster than DELETE for large tables. It cannot be targeted with a WHERE clause — it always clears the entire table.

TRUNCATE TABLE session_log;

-- Also reset the auto-increment counter (useful in test/dev environments)
TRUNCATE TABLE products RESTART IDENTITY;

ON CONFLICT (UPSERT)

A common pattern in real applications is “insert this row, but if a conflict occurs on a unique key, update the existing row instead.” This is called an upsert, and PostgreSQL implements it cleanly with ON CONFLICT. It avoids the race condition that exists when you try to SELECT first, then INSERT or UPDATE based on the result.

ON CONFLICT DO NOTHING

Silently ignore the insert if a conflicting row already exists. Useful for idempotent seed scripts or deduplicating event streams.

INSERT INTO feature_flags (name, enabled)
VALUES ('dark_mode', TRUE)
ON CONFLICT (name) DO NOTHING;

ON CONFLICT DO UPDATE

Update the existing row when a conflict occurs. The special EXCLUDED table refers to the row that was proposed for insertion but rejected due to the conflict.

-- Increment a counter — works whether the row exists or not
INSERT INTO product_stats (product_id, total_sold)
VALUES (5, 10)
ON CONFLICT (product_id)
DO UPDATE SET total_sold = product_stats.total_sold + EXCLUDED.total_sold;

-- Sync a user record from an external source
INSERT INTO users (external_id, name, email, updated_at)
VALUES ('ext-001', 'Bob Smith', '[email protected]', NOW())
ON CONFLICT (external_id)
DO UPDATE SET
    name       = EXCLUDED.name,
    email      = EXCLUDED.email,
    updated_at = EXCLUDED.updated_at;

The conflict target (the column listed after ON CONFLICT) must reference a column with a UNIQUE or PRIMARY KEY constraint.

Transactions for Safety

When running multiple related write operations, wrap them in a transaction so they either all succeed or all roll back. This prevents partial writes — like an order with no items, or stock decremented without an order being recorded.

BEGIN;

INSERT INTO orders (customer_id, total_amount)
VALUES (42, 259.98)
RETURNING id;  -- suppose this returns id = 9001

INSERT INTO order_items (order_id, product_id, quantity, unit_price)
VALUES (9001, 1, 2, 129.99);

-- Decrement stock atomically with the order
UPDATE products SET stock = stock - 2 WHERE id = 1;

COMMIT;
-- If anything fails between BEGIN and COMMIT, run ROLLBACK to undo all changes