BigQuery's SQL dialect looks like standard SQL until you hit the first backtick-quoted table name, the first STRUCT column, or a query that silently costs real money because it scanned an entire table. If you're coming from PostgreSQL or SQL Server, BigQuery syntax is close enough to be dangerous — close enough that you'll write something that looks correct and either fails to parse or runs needlessly expensive.
The differences aren't cosmetic. BigQuery quotes identifiers differently, uses its own type names, models one-to-many data with native STRUCT and ARRAY types instead of joins, doesn't enforce primary or foreign keys, reverses the argument order in common date functions, and bills you by bytes scanned rather than compute time. Get any of these wrong and you'll either see a parser error or, worse, a query that runs fine and costs more than it should.
This is a syntax map for engineers who already know PostgreSQL or T-SQL and need to write correct, cost-aware BigQuery SQL today — not a full BigQuery tutorial. Each difference below comes with side-by-side SQL so you can see exactly what changes.
How do identifiers and table names differ in BigQuery?
PostgreSQL quotes identifiers with double quotes and SQL Server uses square brackets; BigQuery uses backticks, the same convention MySQL uses. BigQuery table references are also usually three-part — project.dataset.table — because a query can reach across projects, not just schemas within one database.
Backticks are only for identifiers — string literals still use single quotes in BigQuery, same as Postgres. If you're unsure when to quote which, our guide to single vs double quotes in SQL covers the general rule before you add BigQuery's backtick convention on top. See Google's lexical structure reference for the full identifier-quoting rules.
What data types replace VARCHAR, UUID, and BIT in BigQuery?
BigQuery collapses most numeric types into two: INT64 for whole numbers and FLOAT64, or the exact NUMERIC and BIGNUMERIC types, for decimals. INT, SMALLINT, BIGINT, and TINYINT all become INT64. There's no VARCHAR or NVARCHAR — everything text-like is STRING, with no length to declare. There's also no UUID type and no BIT type; use STRING, often with GENERATE_UUID(), and BOOL instead.
NUMERIC and BIGNUMERIC behave like Postgres's NUMERIC and SQL Server's DECIMAL — fixed precision, no floating-point rounding surprises. Reach for FLOAT64 only when you genuinely want floating-point behavior.
How do STRUCT and ARRAY change the way you model data?
PostgreSQL and SQL Server model one-to-many relationships with a second table and a JOIN. BigQuery lets a single column hold a STRUCT, a named bundle of fields, or an ARRAY, a repeated list of values of one type, directly inside a row — and a column can be both, an ARRAY of STRUCTs. This is a genuine modeling choice, not just syntax: it trades normalization for fewer joins and often less data scanned.
UNNEST flattens an array so each element becomes its own row — one order with three items produces three output rows. It's the BigQuery equivalent of joining against a child table, except the child table lives inside the parent row.
Nesting isn't free, though. Deeply nested STRUCTs make ad hoc exploration harder for anyone not used to UNNEST, so reach for it when the data is genuinely one-to-many and usually queried together — order line items, event properties — not for relationships you still need to query independently.
Does BigQuery enforce primary keys, foreign keys, or indexes?
You can declare PRIMARY KEY and FOREIGN KEY constraints in BigQuery, and the optimizer uses them for join elimination and reordering — but they're not enforced. BigQuery will not stop you from inserting an order whose customer_id doesn't exist. In Postgres or SQL Server, that insert fails immediately.
There's also no CREATE INDEX in BigQuery. Instead of row-level indexes, you get table-level partitioning, splitting a table by a date or integer range, and clustering, co-locating rows by column value within each partition. Both are declared at table-creation time and both aim to cut the amount of data a query scans, not to speed up point lookups. Clustering narrows scans further within a partition, so a column you filter on constantly — customer_id, say — is worth clustering on even after the table is already partitioned by date. See Google's primary and foreign key docs for exactly what the optimizer does and doesn't rely on them for.
How do date and timestamp functions differ?
Function names mostly translate, but argument order doesn't always match. PostgreSQL's date_trunc takes the unit first, as a string, then the timestamp. BigQuery's DATE_TRUNC takes the date expression first, then an unquoted unit keyword — reversed, and unquoted.
Date-difference functions diverge too. SQL Server's DATEDIFF takes the unit, then the start date, then the end date. BigQuery's DATE_DIFF takes the two dates first and the unit last.
Check the PostgreSQL date and time function reference and the SQL Server DATEDIFF reference side by side with BigQuery's own docs before you port a report full of these calls — a swapped argument order won't always throw an error, it'll just quietly return the wrong number.
What's the difference between BigQuery Standard SQL and Legacy SQL?
BigQuery shipped with a proprietary dialect, now called Legacy SQL, before adopting an ANSI-compliant dialect called Standard SQL, or GoogleSQL. You'll still find Legacy SQL in old tutorials, saved queries, and scheduled jobs, so it's worth recognizing even though you should never write new Legacy SQL.
The gotcha: a comma between two tables in the FROM clause means UNION ALL in Legacy SQL but a JOIN in Standard SQL — the same query text means something different depending on which dialect runs it. Legacy SQL also separates project and dataset with a colon instead of a dot. If a query behaves strangely and you didn't write it, check which dialect it's running under before you debug the logic. Our guide to fixing BigQuery syntax errors covers the specific error messages this mismatch throws.
How does BigQuery's cost model change the way you write queries?
Postgres and SQL Server bill you, indirectly, for compute time and instance size. BigQuery on-demand pricing bills by bytes scanned, regardless of how long the query takes. A broad SELECT on a large, unpartitioned table can cost real money even if it returns in two seconds.
Wrapping a partition column in a function, like extracting the year from a timestamp, forces BigQuery to read every row before it can filter, defeating partition pruning. Filtering on the raw column instead lets BigQuery skip any partition outside the range entirely — same result, a fraction of the bytes scanned. Partition on a DATE or TIMESTAMP column at table-creation time, cluster on your common filter columns, and select only the columns you need.
Both the BigQuery console and the bq command-line tool can dry-run a query and report exactly how many bytes it will scan before you spend anything. Run a ported query through a dry run before you schedule it, not after the bill shows up.
Checklist: porting SQL from Postgres or SQL Server to BigQuery
Swap double quotes or brackets for backticks, and switch to fully qualified project.dataset.table names.
Replace VARCHAR, NVARCHAR, UUID, and BIT with STRING, STRING or GENERATE_UUID(), and BOOL.
Look for one-to-many child tables that could become an ARRAY of STRUCT instead of a join.
Don't rely on PRIMARY KEY or FOREIGN KEY for data integrity — they're declared, not enforced; validate in your pipeline instead.
Re-check every DATE_TRUNC, DATE_DIFF, and EXTRACT call — BigQuery's argument order isn't the same as Postgres's or SQL Server's.
Confirm the query runs under Standard SQL — watch for colon-separated table names or bracket-and-comma patterns that signal Legacy SQL.
Add partitioning and clustering to large tables, and replace broad SELECT * queries with explicit columns before you schedule a job.
Run the ported query through a formatter and validator that understands BigQuery syntax so quoting and typo mistakes surface before you run them against billed data.
Wrapping up
None of these differences are exotic — they're the handful of things that break a straight copy-paste from Postgres or SQL Server into BigQuery: quoting, types, nested data, unenforced keys, function argument order, dialect flags, and cost. Once you know all seven, porting a query becomes mechanical instead of a guessing game.
Paste your BigQuery, PostgreSQL, or SQL Server SQL into sqlfmt to format it and catch misspelled keywords before you run it — it supports all five dialects, including BigQuery. And if MySQL is also part of your stack, our MySQL vs PostgreSQL syntax differences guide covers that migration path too.