Porting a real 800-action BigQuery Dataform project (acuantia) to Supabase produced a catalogue of BigQuery→PostgreSQL rewrites. Almost all of them are deterministic, and almost none are in SQL_RULES. Every one is a rule the next customer will otherwise rediscover by hand.
Fix first: SAFE_CAST is actively harmful
{pattern: /\bSAFE_CAST\s*\(/g,replacement: "CAST(",kind: "rewrite",note: "SAFE_CAST → CAST — Postgres CAST raises on bad input; verify the data or guard it"}This silently strips the safety and leaves a note asking the user to verify. What it actually produces is a run that fails hours later, in a different file, with invalid input syntax for type bigint: "0750069967 " — and the failing SQL is code the converter itself wrote. In acuantia the NULL-on-bad-input behaviour was load-bearing: the sources carry Excel error strings (#DIV/0!, #N/A), SAP identifiers with stray characters, and non-numeric HubSpot team ids.
PostgreSQL 16 added pg_input_is_valid(text, type), which asks exactly the question SAFE_CAST asks:
SAFE_CAST(x AS t) -> case when pg_input_is_valid(x::text, 't') then x::t end
Guard on the text form, cast x itself, so an already-numeric value converts normally. Rewrite to this rather than flagging — it is exact, not approximate. (One divergence to note in the docs: for a fractional numeric cast to an integer type, BigQuery rounds where this yields NULL.)
Rules to add as kind: "rewrite"
All deterministic, all verified against a real project and a live PostgreSQL:
| BigQuery | PostgreSQL | notes |
|---|
SAFE_DIVIDE(a, b) | a / nullif(b, 0) | currently only flagged |
REGEXP_EXTRACT(x, p) | substring(x from p) | both return the first capture group, else the whole match |
DATE_SUB/ADD(d, INTERVAL n u) | (d -/+ interval 'n u') | |
DATE_DIFF(a, b, DAY) | (a::date - b::date) | |
DATE_DIFF(a, b, HOUR) | epoch of the hour-truncated difference | BigQuery counts boundaries crossed; dividing the raw interval is off by one |
arr[OFFSET(n)] / [SAFE_OFFSET(n)] | arr[n + 1] | currently only flagged |
SPLIT(x, d) | string_to_array(x, d) | |
SPLIT(x, '') | string_to_array(x, NULL) | empty delimiter splits per character; an empty string in PG returns one element |
EXTRACT(DAYOFWEEK FROM x) | (extract(dow from x) + 1) | BigQuery is 1-based from Sunday, dow 0-based |
COLLATE(x, '') / x COLLATE '' | x | collation stripping; the function form is a syntax error in PG |
CAST(… AS DATETIME) | … as timestamp | |
`ident` | "ident" | skip project.dataset.table and interpolated tokens |
r'…' | '…' | PG reads the r as a type name |
# comment | -- comment | |
"literal" in value position | 'literal' | IN-lists, equality, aggregate separators only |
GROUP BY ALL | group by 1, 2, 5 | ordinals of non-aggregate, non-window items |
SELECT * EXCEPT (…) | explicit column list | see below |
… NOT ENFORCED | commented-out PG constraint | see below |
Two of these need more than lexical matching, and are worth it — they were the two largest classes in acuantia:
SELECT * EXCEPT (141 sites / 132 files) needs the star's relation resolved: a ref to a declaration gives columnTypes; a ref to a project view resolves recursively; CTEs and subqueries resolve in-file; a qualified star binds through the FROM/JOIN chain. It must sweep to a fixpoint, since a star over a view is only knowable once that view names its own columns. 126 of 141 resolved automatically.
Identifier casing — the extract creates columns with quoteIdent(), so BigQuery's casing survives, while PostgreSQL folds unquoted identifiers to lowercase AND BigQuery matched case-insensitively. So Email must be quoted, email referring to it must become "Email", and a bare quoted column in a select list should alias back to lowercase so the case-folding stops at the source boundary instead of propagating through the graph. ~700 references in acuantia. Needs a real parser, not regex — my regex version was bitten twice by aliases shadowing source column names.
Keep as kind: "flag" — these need intent, not syntax
STRUCT (jsonb vs composite vs restructure depends on downstream), QUALIFY, FARM_FINGERPRINT (no equivalent; whether values must match BigQuery's is a business question), PARSE_DATE/FORMAT_DATE (format tokens are translatable but silently wrong-on-some-rows if mismatched), UNNEST. Their notes should point at a documented worked example rather than "consider jsonb".
Two structural findings
The converter must also rewrite includes/. Shared JS helpers emit SQL, and being template literals they are invisible to a .sqlx scan. In acuantia, split(x, d)[SAFE_OFFSET(n)] inside four helper functions failed two views on every single run while looking like an unfixed tail item. The file has a SQLANVIL-MIGRATE header comment listing findings, so the converter knows they exist — it just cannot act on them.
A --report output would carry more weight than any single rule. What made acuantia tractable was not knowing the rewrites; it was a classified inventory — every remaining construct grouped by class, with counts, file lists and a recommended treatment. That converts an unbounded slog into a finite list someone can work or descope. The information already exists in the findings pass; it just needs emitting as structured output rather than only as inline markers.
Porting a real 800-action BigQuery Dataform project (acuantia) to Supabase produced a catalogue of BigQuery→PostgreSQL rewrites. Almost all of them are deterministic, and almost none are in
SQL_RULES. Every one is a rule the next customer will otherwise rediscover by hand.Fix first: SAFE_CAST is actively harmful
This silently strips the safety and leaves a note asking the user to verify. What it actually produces is a run that fails hours later, in a different file, with
invalid input syntax for type bigint: "0750069967 "— and the failing SQL is code the converter itself wrote. In acuantia the NULL-on-bad-input behaviour was load-bearing: the sources carry Excel error strings (#DIV/0!,#N/A), SAP identifiers with stray characters, and non-numeric HubSpot team ids.PostgreSQL 16 added
pg_input_is_valid(text, type), which asks exactly the question SAFE_CAST asks:Guard on the text form, cast x itself, so an already-numeric value converts normally. Rewrite to this rather than flagging — it is exact, not approximate. (One divergence to note in the docs: for a fractional numeric cast to an integer type, BigQuery rounds where this yields NULL.)
Rules to add as
kind: "rewrite"All deterministic, all verified against a real project and a live PostgreSQL:
SAFE_DIVIDE(a, b)a / nullif(b, 0)REGEXP_EXTRACT(x, p)substring(x from p)DATE_SUB/ADD(d, INTERVAL n u)(d -/+ interval 'n u')DATE_DIFF(a, b, DAY)(a::date - b::date)DATE_DIFF(a, b, HOUR)arr[OFFSET(n)]/[SAFE_OFFSET(n)]arr[n + 1]SPLIT(x, d)string_to_array(x, d)SPLIT(x, '')string_to_array(x, NULL)EXTRACT(DAYOFWEEK FROM x)(extract(dow from x) + 1)dow0-basedCOLLATE(x, '')/x COLLATE ''xCAST(… AS DATETIME)… as timestamp`ident`"ident"project.dataset.tableand interpolated tokensr'…''…'ras a type name# comment-- comment"literal"in value position'literal'GROUP BY ALLgroup by 1, 2, 5SELECT * EXCEPT (…)… NOT ENFORCEDTwo of these need more than lexical matching, and are worth it — they were the two largest classes in acuantia:
SELECT * EXCEPT(141 sites / 132 files) needs the star's relation resolved: a ref to a declaration givescolumnTypes; a ref to a project view resolves recursively; CTEs and subqueries resolve in-file; a qualified star binds through the FROM/JOIN chain. It must sweep to a fixpoint, since a star over a view is only knowable once that view names its own columns. 126 of 141 resolved automatically.Identifier casing — the extract creates columns with
quoteIdent(), so BigQuery's casing survives, while PostgreSQL folds unquoted identifiers to lowercase AND BigQuery matched case-insensitively. SoEmailmust be quoted,emailreferring to it must become"Email", and a bare quoted column in a select list should alias back to lowercase so the case-folding stops at the source boundary instead of propagating through the graph. ~700 references in acuantia. Needs a real parser, not regex — my regex version was bitten twice by aliases shadowing source column names.Keep as
kind: "flag"— these need intent, not syntaxSTRUCT(jsonb vs composite vs restructure depends on downstream),QUALIFY,FARM_FINGERPRINT(no equivalent; whether values must match BigQuery's is a business question),PARSE_DATE/FORMAT_DATE(format tokens are translatable but silently wrong-on-some-rows if mismatched),UNNEST. Their notes should point at a documented worked example rather than "consider jsonb".Two structural findings
The converter must also rewrite
includes/. Shared JS helpers emit SQL, and being template literals they are invisible to a.sqlxscan. In acuantia,split(x, d)[SAFE_OFFSET(n)]inside four helper functions failed two views on every single run while looking like an unfixed tail item. The file has aSQLANVIL-MIGRATEheader comment listing findings, so the converter knows they exist — it just cannot act on them.A
--reportoutput would carry more weight than any single rule. What made acuantia tractable was not knowing the rewrites; it was a classified inventory — every remaining construct grouped by class, with counts, file lists and a recommended treatment. That converts an unbounded slog into a finite list someone can work or descope. The information already exists in the findings pass; it just needs emitting as structured output rather than only as inline markers.