PostgreSQL Query Formatter — Format and Beautify PostgreSQL SQL Online

PostgreSQL has a richer SQL dialect than most databases, and formatting PostgreSQL queries well means knowing which features to use. CTEs, window functions, JSON operators, and JSONB queries all have their own formatting conventions.

PostgreSQL identifier quoting

PostgreSQL uses double quotes for identifiers (not backticks like MySQL). Unquoted identifiers are case-insensitive and converted to lowercase, so these are equivalent:

-- These three are identical
SELECT User_ID FROM Users;
SELECT user_id FROM users;
SELECT "user_id" FROM "users";

-- This is different (preserves case)
SELECT "User_ID" FROM "Users";

Window functions (PostgreSQL is excellent at these)

SELECT
  id,
  name,
  department,
  salary,
  AVG(salary) OVER (
    PARTITION BY department
    ORDER BY hire_date
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS running_avg_salary,
  RANK() OVER (
    PARTITION BY department
    ORDER BY salary DESC
  ) AS salary_rank
FROM employees
ORDER BY department, salary_rank;

JSONB queries

-- Query JSONB column
SELECT
  id,
  metadata->>'name' AS name,          -- text extraction
  (metadata->>'age')::int AS age,     -- with type cast
  metadata->'address'->>'city' AS city -- nested access
FROM users
WHERE
  metadata @> '{"role": "admin"}'     -- contains operator
  AND metadata ? 'verified'           -- key exists
ORDER BY id;

LATERAL joins for correlated subqueries

-- Get the latest 3 orders per user
SELECT
  u.id,
  u.name,
  recent.id AS order_id,
  recent.total
FROM users u
LEFT JOIN LATERAL (
  SELECT id, total
  FROM orders
  WHERE user_id = u.id
  ORDER BY created_at DESC
  LIMIT 3
) AS recent ON TRUE
ORDER BY u.id, recent.id DESC;

Format PostgreSQL queries before adding them to your migrations or documentation using our SQL Formatter.