SQL dialect differences that affect formatting

The SQL standard uses single quotes for string literals and double quotes for identifiers. PostgreSQL and SQLite follow it: 'text' is a value, "Order Items" is a table name. MySQL uses backticks for identifiers by default and, unless the ANSI_QUOTES mode is enabled, treats double quotes as another kind of string. SQL Server prefers square brackets, as in [Order Items], and accepts double quotes only when QUOTED_IDENTIFIER is on, which is the default for most connections.

For a formatter this is the most important difference. A backtick or a square bracket that the tokenizer does not expect breaks the query into nonsense pieces. If a query uses backticks, choose MySQL; if it uses square brackets, choose T-SQL. SQLite is lenient and accepts all three identifier styles for compatibility.

Double-dash line comments and slash-star block comments work everywhere, with small twists. MySQL requires a space or control character after the two dashes, so --comment without a space is not a comment there, and it also accepts # as a line comment. PostgreSQL allows block comments to nest, which means /* outer /* inner */ still outer */ is a single comment.

Escaping inside strings differs too. Every dialect accepts a doubled quote, as in 'it''s', which is the portable choice. MySQL additionally treats the backslash as an escape character by default, so 'C:\new' contains a newline there but a literal backslash in PostgreSQL. PostgreSQL only interprets backslashes in E'...' strings.

PostgreSQL adds several constructs a generic tokenizer may not recognise. The :: operator is a type cast, as in created_at::date. Dollar-quoted strings such as $$ ... $$ or $body$ ... $body$ hold function bodies without any escaping. Positional parameters in prepared statements look like $1, $2. JSON operators include ->, ->>, #> and @>, and ILIKE is case-insensitive LIKE.

Selecting the PostgreSQL dialect keeps these intact: a cast stays glued to its expression, a dollar-quoted body is copied verbatim, and RETURNING, ON CONFLICT and DISTINCT ON are treated as clauses rather than random words.

T-SQL is the most distinct of the four. Local variables start with @ and system functions with @@, as in @@ROWCOUNT. Row limits are written as SELECT TOP 10 instead of LIMIT, or with OFFSET ... FETCH NEXT ... ROWS ONLY after ORDER BY. Scripts are split into batches with GO, which is a command of the client tools rather than a SQL keyword.

Temporary tables are named with # and global ones with ##, so a formatter must not mistake them for comments. Choosing T-SQL makes sure that #orders stays a table name and that DECLARE, SET and BEGIN ... END blocks are laid out as statements.

Beyond backticks, MySQL has user variables such as @total, the := assignment operator, and LIMIT offset, count as an alternative to LIMIT ... OFFSET. SQLite is the most permissive: it accepts most standard syntax, the three identifier quote styles, and parameters written as ?, ?1, :name or @name.

When you are not sure where a query came from, start with Standard SQL. If the formatter reports a parse error, the error position usually points at the dialect-specific token, and switching to the matching dialect resolves it.

If a query arrives without context, look for marker tokens. Backticks, # comments or LIMIT 10, 20 point to MySQL. Double-quoted mixed-case names, :: casts, ILIKE or RETURNING point to PostgreSQL. Square brackets, TOP, @variables or GO point to SQL Server. Question-mark parameters with otherwise plain syntax often come from SQLite or an application driver.