UPDATE and DELETE

PostgreSQL UPDATE modifies existing rows and supports a FROM clause that lets you join other tables to determine which rows to update and what values to set — this is a non-standard but very useful extension. Like INSERT, UPDATE and DELETE support RETURNING to get the modified rows back without a second query. DELETE with a USING clause allows filtering based on another table. TRUNCATE is much faster than DELETE for removing all rows from a table because it bypasses row-level processing and does not fire per-row triggers, but it cannot be filtered and does not record individual row deletions in the WAL. PostgreSQL also supports UPDATE...SET from a subquery or CTE for complex value calculations.

Example

-- Basic UPDATE
UPDATE products
  SET price = price * 1.10, updated_at = NOW()
WHERE category_id = 3;

-- UPDATE with RETURNING
UPDATE users
  SET last_login = NOW()
WHERE email = 'alice@example.com'
RETURNING id, name, last_login;

-- UPDATE with FROM (join-based update)
UPDATE order_items oi
  SET discounted_price = oi.unit_price * (1 - d.rate)
FROM discounts d
WHERE d.product_id = oi.product_id
  AND d.expires_at > NOW();

-- DELETE with RETURNING
DELETE FROM sessions
WHERE expires_at < NOW()
RETURNING session_id, user_id;

-- DELETE with USING (join-based delete)
DELETE FROM order_items oi
USING orders o
WHERE oi.order_id = o.id
  AND o.status = 'cancelled';

-- TRUNCATE — fast bulk delete (use with care)
TRUNCATE TABLE temp_import_data;

-- TRUNCATE with CASCADE (also truncates referencing tables)
TRUNCATE TABLE orders CASCADE;

HTML

Full Editor

Related Resources

PostgreSQL Reference

Complete tag & property list

PostgreSQL How-To Guides

Step-by-step practical guides

PostgreSQL Exercises

Practice what you've learned