INSERT, UPDATE, and DELETE

Writing Data with DML

DML (Data Manipulation Language) is the subset of SQL that modifies data: INSERT adds new rows, UPDATE changes existing rows, and DELETE removes rows. These three statements, combined with SELECT, form the complete set of CRUD (Create, Read, Update, Delete) operations.

Understanding how to use these safely — including the RETURNING clause, upserts, and the importance of WHERE conditions — is critical for building reliable data layers.

INSERT — Adding New Rows

The INSERT statement adds one or more rows to a table. You specify the target columns and the corresponding values:

INSERT INTO table_name (column1, column2, column3)
VALUES (value1, value2, value3);

Columns with DEFAULT values or SERIAL auto-increment can be omitted:

-- Only supply the columns that don't have defaults
INSERT INTO products (name, category, price)
VALUES ('Webcam HD', 'electronics', 79.99);
-- stock will use its DEFAULT value, created_at will use NOW()

Inserting Multiple Rows

Inserting rows one at a time in a loop is inefficient. Use a single multi-row INSERT for bulk operations:

INSERT INTO products (name, category, price, stock)
VALUES
    ('Monitor 27"',      'electronics', 399.99, 20),
    ('Webcam 4K',        'electronics', 129.99, 35),
    ('Mousepad XL',      'accessories',  19.99, 100),
    ('Cable Organizer',  'accessories',   9.99, 150);

PostgreSQL handles all four rows in a single transaction, which is significantly faster than four separate INSERT statements because it reduces round trips to the server and amortizes transaction overhead.

INSERT ... SELECT

You can populate a table from the results of a SELECT query:

-- Copy all electronics products to an archive table
INSERT INTO products_archive (name, category, price, archived_at)
SELECT name, category, price, NOW()
FROM products
WHERE category = 'electronics';

This is useful for archiving old data, populating denormalized tables, or seeding test databases.

RETURNING — Getting Generated Values Back

After an INSERT, you often need the auto-generated primary key to use in subsequent operations (for example, to insert related rows in a child table). The RETURNING clause returns the affected row(s):

INSERT INTO customers (email, full_name)
VALUES ('[email protected]', 'Bob Smith')
RETURNING customer_id, email, created_at;

Result:

 customer_id |       email       |         created_at
-------------+-------------------+----------------------------
           5 | [email protected]   | 2026-04-02 10:30:00+00

Without RETURNING, you would need a separate SELECT query to find the new ID, which introduces a race condition in concurrent environments and wastes a network round trip.

UPDATE — Modifying Existing Rows

The UPDATE statement changes the values of columns in existing rows:

UPDATE table_name
SET column1 = value1, column2 = value2
WHERE condition;

Updating Multiple Columns

UPDATE products
SET price = 44.99,
    stock = 100,
    updated_at = NOW()
WHERE product_id = 3;

Updating Based on Other Columns

You can reference the current value of a column in the new value expression:

-- Apply a 10% discount to all furniture
UPDATE products
SET price = price * 0.90
WHERE category = 'furniture';

-- Decrease stock by quantity sold
UPDATE products
SET stock = stock - 1
WHERE product_id = 7 AND stock > 0;

UPDATE with a Subquery

Update rows based on data from another table:

-- Set all products' status to 'out_of_stock' if their stock is 0
UPDATE products
SET status = 'out_of_stock'
WHERE stock = 0;

-- Update using a correlated subquery
UPDATE products p
SET last_order_date = (
    SELECT MAX(ordered_at)
    FROM order_items oi
    JOIN orders o ON oi.order_id = o.order_id
    WHERE oi.product_id = p.product_id
);

UPDATE ... RETURNING

Like INSERT, UPDATE supports RETURNING to see the modified rows:

UPDATE products
SET price = price * 1.05
WHERE category = 'electronics'
RETURNING product_id, name, price AS updated_price;

This is invaluable for auditing changes and for confirming that your WHERE clause targeted exactly the rows you intended.

DELETE — Removing Rows

The DELETE statement removes rows that satisfy a condition:

DELETE FROM table_name
WHERE condition;

Safe DELETE Pattern

Always follow this two-step approach for important deletes:

-- Step 1: Run the equivalent SELECT to preview what will be deleted
SELECT product_id, name, category
FROM products
WHERE stock = 0 AND updated_at < NOW() - INTERVAL '90 days';

-- Step 2: If the results look correct, run the DELETE
DELETE FROM products
WHERE stock = 0 AND updated_at < NOW() - INTERVAL '90 days';

DELETE ... RETURNING

-- Delete and retrieve the deleted rows for logging
DELETE FROM sessions
WHERE expires_at < NOW()
RETURNING session_id, user_id, created_at;

TRUNCATE — Fast Bulk Delete

If you need to delete all rows from a table (for example, to reset test data), TRUNCATE is much faster than DELETE FROM table:

-- Removes all rows instantly, resets SERIAL sequences
TRUNCATE TABLE sessions RESTART IDENTITY;

-- Truncate multiple tables at once (respects foreign key order)
TRUNCATE TABLE order_items, orders, customers RESTART IDENTITY CASCADE;

TRUNCATE is not transactional in some databases, but in PostgreSQL it is fully ACID-compliant and can be rolled back within a transaction.

UPSERT — INSERT or UPDATE

A common pattern in applications is "insert if not exists, update if it does." PostgreSQL handles this elegantly with ON CONFLICT:

-- Insert a product; if the name conflicts, update price and stock
INSERT INTO products (name, category, price, stock)
VALUES ('Wireless Mouse', 'electronics', 34.99, 200)
ON CONFLICT (name)
DO UPDATE SET
    price      = EXCLUDED.price,
    stock      = EXCLUDED.stock,
    updated_at = NOW();

EXCLUDED refers to the row that was proposed for insertion. You can also use ON CONFLICT DO NOTHING to silently ignore conflicts:

-- Insert if not exists, ignore if already there
INSERT INTO user_badges (user_id, badge_id, earned_at)
VALUES (42, 7, NOW())
ON CONFLICT (user_id, badge_id) DO NOTHING;

ON CONFLICT requires specifying either a unique column name or a constraint name. The conflict target must match an existing UNIQUE or PRIMARY KEY constraint.

Bulk Operations and Performance

For loading large amounts of data, PostgreSQL's COPY command is far faster than multi-row INSERT:

-- Load from a CSV file (server-side)
COPY products (name, category, price, stock)
FROM '/tmp/products.csv'
WITH (FORMAT csv, HEADER true, DELIMITER ',');

-- Load from stdin (client-side, useful with psql \copy)
-- \copy products FROM 'local_file.csv' WITH (FORMAT csv, HEADER true)

COPY bypasses the SQL parser and constraint checking for individual rows, making it orders of magnitude faster for bulk loads. For production ETL pipelines, always use COPY over multi-row INSERT when loading thousands or millions of rows.

With INSERT, UPDATE, DELETE, and UPSERT in your toolkit, you can implement complete CRUD operations. The next lesson introduces aggregate functions for summarizing and analyzing data at scale.