MySQL vs PostgreSQL vs BigQuery SQL Formatting

SQL is theoretically standardised. In practice, MySQL, PostgreSQL, and BigQuery each have syntax quirks, quoting conventions, and function names that differ enough to matter. Using the wrong dialect in a formatter produces output that's syntactically valid for the formatter but wrong for your actual database.

Identifier quoting

The first practical difference: how each database quotes identifiers (table names, column names) that conflict with reserved words or contain spaces.

MySQL uses backticks: `user`, `order`. Using double quotes in MySQL either causes an error or treats the double-quoted string as a value (depending on the SQL mode).

PostgreSQL uses double quotes: "user", "order". Backticks in PostgreSQL cause a syntax error.

BigQuery uses backticks for project.dataset.table references: `my-project.my_dataset.my_table`. Standard column identifiers follow ANSI SQL (double quotes), but in practice most BigQuery SQL is written without any quoting on identifiers.

Case sensitivity

MySQL table names are case-sensitive on Linux file systems and case-insensitive on Windows and macOS. Column names are always case-insensitive. This creates portability problems when code developed on macOS is deployed to Linux.

PostgreSQL folds unquoted identifiers to lowercase. SELECT UserName FROM Users becomes SELECT username FROM users. Quoted identifiers are case-sensitive: "UserName" is different from "username".

BigQuery is case-insensitive for identifiers and keywords.

LIMIT and OFFSET

-- MySQL and PostgreSQL: same syntax
SELECT * FROM orders LIMIT 10 OFFSET 20;

-- BigQuery: same syntax, but OFFSET is less commonly used
SELECT * FROM orders LIMIT 10 OFFSET 20;

-- BigQuery also supports:
SELECT * FROM orders LIMIT 10;  -- no OFFSET needed for simple cases

Date and time functions

This is where dialects diverge most significantly:

-- Current timestamp
MySQL:      NOW()
PostgreSQL: NOW() or CURRENT_TIMESTAMP
BigQuery:   CURRENT_TIMESTAMP()

-- Extract part of a date
MySQL:      YEAR(created_at)
PostgreSQL: EXTRACT(YEAR FROM created_at) or DATE_PART('year', created_at)
BigQuery:   EXTRACT(YEAR FROM created_at)

-- Add an interval
MySQL:      DATE_ADD(created_at, INTERVAL 7 DAY)
PostgreSQL: created_at + INTERVAL '7 days'
BigQuery:   DATE_ADD(created_at, INTERVAL 7 DAY)