Resolved by removing the class rather than reconciling it (d8c4322).
The premise here was that BigQuery's casing has to be carried into PostgreSQL and every reference quoted to match — ~700 references across 49 files in the acuantia port, with quoted names then propagating through the graph and needing to be aliased back to lower case at the source boundary so downstream reads did not break.
Checking what PostgreSQL actually offers settled it: there is no case-insensitive identifier mode — no GUC, no initdb flag, it is parser-level. (Case-insensitive data is available per column via citext or a non-deterministic ICU collation, but PostgreSQL will not accept a non-deterministic collation as a database default, and it is irrelevant to identifiers anyway.)
So the only way to get case-insensitive behaviour is for the identifiers to be lower case. Which they can be, at the point they are created:
extract_load.foldColumns maps each source column to its materialized identifier; the loader creates and inserts using the folded name.introspect writes columnTypes folded, so a declaration describes the table as it exists in the WRITE warehouse — what the project's SQL actually reads.
The SQL BigQuery accepted then keeps working unquoted, because BigQuery matched case-insensitively and PostgreSQL folds to the same lower case. No rewriting, no drift, no alias-shadowing heuristics — and lower-case identifiers are the PostgreSQL convention regardless.
Two things worth recording for anyone who revisits this:
The source spelling has to be retained for reading. Rows arrive keyed by the ORIGINAL name, so reading them by the folded name yields undefined for every row — a table that loads full of NULLs and never errors. It has its own test for that reason.
Columns differing only in case now fail loudly, naming both, rather than one silently overwriting the other.
The trade-off accepted: column names in the write warehouse no longer match the source spelling. Mid-migration nothing reads them by the old name, and the alternative was a permanent quoting obligation on every future reference.
Resolved by removing the class rather than reconciling it (d8c4322).
The premise here was that BigQuery's casing has to be carried into PostgreSQL and every reference quoted to match — ~700 references across 49 files in the acuantia port, with quoted names then propagating through the graph and needing to be aliased back to lower case at the source boundary so downstream reads did not break.
Checking what PostgreSQL actually offers settled it: there is no case-insensitive identifier mode — no GUC, no initdb flag, it is parser-level. (Case-insensitive data is available per column via
citextor a non-deterministic ICU collation, but PostgreSQL will not accept a non-deterministic collation as a database default, and it is irrelevant to identifiers anyway.)So the only way to get case-insensitive behaviour is for the identifiers to be lower case. Which they can be, at the point they are created:
extract_load.foldColumnsmaps each source column to its materialized identifier; the loader creates and inserts using the folded name.introspectwritescolumnTypesfolded, so a declaration describes the table as it exists in the WRITE warehouse — what the project's SQL actually reads.The SQL BigQuery accepted then keeps working unquoted, because BigQuery matched case-insensitively and PostgreSQL folds to the same lower case. No rewriting, no drift, no alias-shadowing heuristics — and lower-case identifiers are the PostgreSQL convention regardless.
Two things worth recording for anyone who revisits this:
The source spelling has to be retained for reading. Rows arrive keyed by the ORIGINAL name, so reading them by the folded name yields undefined for every row — a table that loads full of NULLs and never errors. It has its own test for that reason.
Columns differing only in case now fail loudly, naming both, rather than one silently overwriting the other.
The trade-off accepted: column names in the write warehouse no longer match the source spelling. Mid-migration nothing reads them by the old name, and the alternative was a permanent quoting obligation on every future reference.