SQLsql-formatterField notes

Six places sql-formatter still trips up, and what to do about each

What we found running real PostgreSQL, T-SQL, MySQL, Oracle and BigQuery queries through sql-formatter while writing this site’s dialect pages — with the actual output and a workaround for each case.

Written and maintained by Pura IAOctober 9, 20264 min read

Every SQL formatter on this site is the open-source sql-formatter library, version 15.6.12 at the time of writing, running in your browser. It is a good library, and this is not a takedown. But when we wrote the dialect pages, we made one rule: every "formatted output" shown on a page had to be produced by the library and checked by a test, never typed by hand. That rule surfaced a handful of cases where the output is valid SQL but not what a person would write — and in one case, not what the query means.

All of the outputs below were generated with the options the site opens with: keywords, functions and data types in upper case, identifiers left as typed, two-space indentation. Paste any of them into the SQL formatter and you will see the same thing.

1. T-SQL: TOP (n) WITH TIES is split into three lines

This is the only case on the list where the output reads as a different statement. The parser sees WITH and treats it as the start of a common table expression, so the clause is broken apart:

Input

select top (3) with ties name, score from players order by score desc;

sql-formatter output (transactsql)

SELECT
  TOP (3)
WITH
  ties name,
  score
FROM
  players
ORDER BY
  score DESC;

Dropping the parentheses (TOP 3) does not help — the same split happens. What does work is the library’s own escape hatch: a region wrapped in /* sql-formatter-disable */ and /* sql-formatter-enable */ comments is passed through untouched, while everything after it is still formatted.

With the disable comments

/* sql-formatter-disable */
select top (3) with ties name, score from players order by score desc;
/* sql-formatter-enable */
SELECT
  a
FROM
  b;

Those comments are plain SQL comments, so SQL Server ignores them, and they work in this site’s formatter as well — you can leave them in a migration file permanently.

2. PostgreSQL: DISTINCT ON is separated from DISTINCT

sql-formatter output (postgresql)

SELECT DISTINCT
  ON (customer_id) customer_id,
  created_at,
  total
FROM
  orders
ORDER BY
  customer_id,
  created_at DESC;

DISTINCT ON (customer_id) is one construct — "keep the first row per customer_id" — but the formatter puts DISTINCT on the SELECT line and starts the next line with ON (customer_id), which makes the select list look like it begins with a stray ON. The query is unchanged and runs fine; it just reads worse than the rest of the output. There is no option that changes this, so if it bothers you in a reviewed file, fix the one line by hand after formatting.

3. Oracle: a column called name becomes NAME

sql-formatter output (plsql)

SELECT
  id,
  NAME
FROM
  employees
ORDER BY
  NAME
FETCH FIRST
  25 rows ONLY;

We asked for identifiers to be preserved, and id and employees were. But name came back as NAME, because the PL/SQL grammar knows NAME as a keyword and keyword casing wins. The same output also leaves rows in lower case inside FETCH FIRST 25 rows ONLY, the opposite inconsistency.

In Oracle this happens to be harmless: unquoted identifiers are case-insensitive and stored in upper case, so name and NAME are the same column. The tempting "fix" is the dangerous one. Writing "name" in double quotes does keep it lower case in the output — but in Oracle a quoted identifier is case-sensitive, so "name" refers to a column literally named in lower case, which in a typical schema does not exist. If the casing matters to you, set keyword casing to Preserve instead; that leaves the whole query exactly as typed.

4. MySQL: INTERVAL keeps whatever case you typed

sql-formatter output (mysql)

SELECT
  id
FROM
  sessions
WHERE
  created_at < DATE_SUB(NOW(), interval 30 day);

Inside DATE_SUB(...), interval 30 day is read as part of the function’s argument rather than as keywords, so keyword casing is not applied to it. The fix is simply to type it the way you want it — the formatter preserves INTERVAL 30 DAY exactly when it is written that way:

Same query, INTERVAL typed in upper case

SELECT
  id
FROM
  sessions
WHERE
  created_at < DATE_SUB(NOW(), INTERVAL 30 DAY);

5. T-SQL MERGE: USING stays lower case and on the target line

sql-formatter output (transactsql)

MERGE INTO
  dbo.stock AS t using dbo.incoming AS s ON t.sku = s.sku
WHEN MATCHED THEN
UPDATE SET
  t.qty = t.qty + s.qty;

The WHEN MATCHED branch gets its own line, which is the part that matters most when reading a MERGE. But using is neither upper-cased nor moved to its own line, so source and target end up on one line. As with INTERVAL, typing USING in upper case at least makes the casing consistent.

6. BigQuery: pseudo-columns are treated as identifiers

sql-formatter output (bigquery)

SELECT
  *
FROM
  `proj.ds.events_*`
WHERE
  _table_suffix BETWEEN '20260101' AND '20260131';

_TABLE_SUFFIX, _PARTITIONTIME and _PARTITIONDATE are documented in upper case, but the formatter reads them as ordinary identifiers and, with identifier casing on Preserve, leaves them as typed. BigQuery accepts any casing for them, so this is cosmetic only.

What the list has in common

Five of the six are about casing or line placement and never change what the query does. The one that reads as a different statement — TOP ... WITH TIES — comes from a keyword (WITH) that means two unrelated things in T-SQL. That is the general shape of formatter bugs: they cluster where a dialect reuses a word, not where its syntax is unusual. The genuinely unusual syntax (PostgreSQL’s :: casts and JSONB operators, BigQuery’s backtick-qualified names, Oracle’s optimizer hints) all came through correctly in our tests.

Each case above is pinned by a test in this site’s repository. When sql-formatter is upgraded and one of them changes, the test fails and this article gets revised — so if you are reading it, it still matches what the tool on this site does.