DELETE FROM

-- Delete specific rows
DELETE FROM sessions WHERE expires_at < NOW();

-- Delete with JOIN (delete based on another table)
DELETE FROM order_items
WHERE order_id IN (
  SELECT id FROM orders WHERE status = 'cancelled' AND created_at < '2024-01-01'
);

-- Soft delete (preferred in production — keeps audit trail)
UPDATE users SET deleted_at = NOW() WHERE id = 42;

-- Hard delete (permanent)
DELETE FROM users WHERE id = 42;

-- Delete with RETURNING (get deleted rows)
DELETE FROM expired_tokens WHERE expires_at < NOW()
RETURNING token_hash, user_id;

-- Safe: preview before deleting
SELECT COUNT(*) FROM logs WHERE created_at < NOW() - INTERVAL '90 days';
-- If count looks right, then:
DELETE FROM logs WHERE created_at < NOW() - INTERVAL '90 days';

-- Soft delete: mark user as deleted (preferred)
UPDATE users
SET
  deleted_at = NOW(),
  email      = email || '.deleted.' || id  -- free up email for re-registration
WHERE id = 42;

-- Query active users excludes soft-deleted
SELECT * FROM users WHERE deleted_at IS NULL;

-- Periodic cleanup: permanently delete old soft-deleted records
-- (run in batches to avoid locking large tables)
DO $$
DECLARE
  batch_size INT := 1000;
  deleted_count INT;
BEGIN
  LOOP
    DELETE FROM users
    WHERE id IN (
      SELECT id FROM users
      WHERE deleted_at < NOW() - INTERVAL '180 days'
      LIMIT batch_size
    );

    GET DIAGNOSTICS deleted_count = ROW_COUNT;
    EXIT WHEN deleted_count < batch_size;

    PERFORM pg_sleep(0.1);  -- brief pause between batches
  END LOOP;
END $$;