SQL Snippet Library

Ready-to-use SQL snippets for common database tasks. Copy, paste, and adapt for your projects.

User Management

Create Users Table

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  email VARCHAR(255) UNIQUE NOT NULL,
  password_hash VARCHAR(255) NOT NULL,
  display_name VARCHAR(100),
  avatar_url TEXT,
  role VARCHAR(20) DEFAULT 'user',
  is_active BOOLEAN DEFAULT TRUE,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Find User by Email

SELECT id, email, display_name, avatar_url, role, created_at
FROM users
WHERE email = LOWER(TRIM(:email))
  AND is_active = TRUE;

Update Last Login

UPDATE users
SET last_login_at = CURRENT_TIMESTAMP,
    updated_at = CURRENT_TIMESTAMP
WHERE id = :user_id;

Pagination

Keyset Pagination (Cursor-based)

SELECT id, title, created_at
FROM posts
WHERE created_at < :cursor
ORDER BY created_at DESC, id DESC
LIMIT :limit;

Offset Pagination

SELECT *
FROM posts
ORDER BY created_at DESC
LIMIT :limit
OFFSET :offset;

Aggregation

Monthly Report

SELECT
  DATE_TRUNC('month', created_at) AS month,
  COUNT(*) AS total_orders,
  SUM(total) AS revenue,
  AVG(total) AS avg_order_value
FROM orders
WHERE created_at >= DATE_TRUNC('year', CURRENT_DATE)
GROUP BY DATE_TRUNC('month', created_at)
ORDER BY month DESC;

Top N per Category

WITH ranked AS (
  SELECT *,
    ROW_NUMBER() OVER (
      PARTITION BY category_id
      ORDER BY revenue DESC
    ) AS rn
  FROM products
)
SELECT id, name, category_id, revenue
FROM ranked
WHERE rn <= 5;

Full-Text Search

PostgreSQL Full-Text Search

SELECT id, title, body,
  ts_rank(to_tsvector('english', title || ' ' || body), plainto_tsquery('english', :query)) AS rank
FROM articles
WHERE to_tsvector('english', title || ' ' || body) @@ plainto_tsquery('english', :query)
ORDER BY rank DESC
LIMIT 20;

Hierarchical Data

Recursive CTE for Tree Structure

WITH RECURSIVE category_tree AS (
  -- Base case: root categories
  SELECT id, parent_id, name, 0 AS depth, name AS path
  FROM categories
  WHERE parent_id IS NULL

  UNION ALL

  -- Recursive case: children
  SELECT c.id, c.parent_id, c.name, ct.depth + 1,
    ct.path || ' > ' || c.name
  FROM categories c
  JOIN category_tree ct ON c.parent_id = ct.id
)
SELECT id, depth, path
FROM category_tree
ORDER BY path;

Audit & History

Created/Updated Trigger Function

CREATE OR REPLACE FUNCTION set_timestamps()
RETURNS TRIGGER AS $$
BEGIN
  NEW.updated_at = CURRENT_TIMESTAMP;
  IF TG_OP = 'INSERT' THEN
    NEW.created_at = CURRENT_TIMESTAMP;
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_set_timestamps
  BEFORE INSERT OR UPDATE ON users
  FOR EACH ROW
  EXECUTE FUNCTION set_timestamps();

JSON Operations

Query JSON Data

-- PostgreSQL JSONB queries
SELECT
  id,
  data->>'name' AS name,
  data->>'email' AS email,
  data->'metadata'->>'source' AS source
FROM events
WHERE data @> '{"type": "purchase"}'
  AND (data->>'amount')::numeric > 100
ORDER BY (data->>'timestamp')::timestamptz DESC;