DELETE

-- Basic
DELETE FROM users WHERE id = 42;

-- RETURNING — get the row(s) back
DELETE FROM users
WHERE id = 42
RETURNING id, email, deleted_at;

-- DELETE … USING — delete based on another table
DELETE FROM posts p
USING  users u
WHERE  p.user_id = u.id
  AND  u.banned = true;

-- LIMIT-like deletes — via CTE (Postgres has no LIMIT on DELETE)
WITH old AS (
    SELECT id FROM logs
    WHERE created_at < now() - INTERVAL '30 days'
    ORDER BY id
    LIMIT 10000
)
DELETE FROM logs WHERE id IN (SELECT id FROM old);

-- TRUNCATE — fast, locks the table, no row triggers
TRUNCATE TABLE sessions RESTART IDENTITY CASCADE;

-- ON DELETE CASCADE — let the schema clean child rows for you
CREATE TABLE posts (
    id BIGSERIAL PRIMARY KEY,
    user_id BIGINT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    ...
);

-- Soft delete — keep the row, mark it deleted
UPDATE users SET deleted_at = now() WHERE id = 42;
-- And exclude by default at read time
SELECT * FROM users WHERE deleted_at IS NULL;

DELETE FROM users
WHERE created_at < now() - interval '1 year'
RETURNING id;