Transpiling DuckDB queries utilizing reusable column aliases produces invalid SQL

[LennartH]

DuckDB allows direct usage of column aliases in the same statement (see this and this). So for example these are valid statements:

SELECT 'foo' as a
WHERE a = 'foo'

SELECT
  i + 1 AS j,
  j + 2 AS k
FROM range(0, 3) t(i)

Transpiling statements like that, for example to postgres, with sqlglot.transpile("...", read="duckdb", write="postgres") keeps these usages which would result in errors like column "a" does not exist when executed.

Now I imagine this isn't trivial to solve for arbitrary SQL, so is this something SQLGlot wants to take into account or does this fall under "However, SQLGlot does not aim to be a SQL validator, so it may fail to detect certain syntax errors"?

[tobymao]

you can use the optimizer qualify columns to remove the aliases usages