PostgreSQL DELETE

Overview

The DELETE statement removes rows from a table. This chapter covers how to delete specific rows and use conditions.

Basic DELETE

-- Delete single row
DELETE FROM users WHERE id = 1;

-- Delete multiple rows
DELETE FROM users WHERE status = 'inactive' AND last_login < '2022-01-01';

DELETE with RETURNING

-- Return deleted values
DELETE FROM users WHERE id = 1
RETURNING id, name, deleted_at;

DELETE with USING

-- Delete using another table
DELETE FROM orders USING users 
WHERE orders.user_id = users.id 
AND users.status = 'banned';

TRUNCATE TABLE

-- Faster than DELETE for all rows
TRUNCATE TABLE logs;

-- Reset auto-increment sequences
TRUNCATE TABLE logs RESTART IDENTITY;

-- Cascade to dependent tables
TRUNCATE TABLE logs CASCADE;

Summary

DELETE key points:

• Use WHERE clause to specify rows to delete
• Use RETURNING clause to get deleted values
• Use TRUNCATE for faster table clearing