column_alias, the SQL spec calls these
<derived column list> clauses. This is what the postgres docs say about them,
A substitute name for the
FROMitem containing the alias. An alias is used for brevity or to eliminate ambiguity for self-joins (where the same table is scanned multiple times). When an alias is provided, it completely hides the actual name of the table or function; for example given
FROM foo AS f, the remainder of the
SELECTmust refer to this
foo. If an alias is written, a column alias list can also be written to provide substitute names for one or more columns of the table.
When are these needed? When can I not otherwise just use a COLUMN alias?
SELECT t.* FROM table_name AS t (a,b,c);
SELECT t.col1 AS a, t.col2 AS b, t.col3 AS c FROM table_name AS t;
This example taken from the chosen answer by @ypercubeᵀᴹ which doesn't seem too useful.
FROM aliases in the above context provide no real benefit unless you're
- relying on partial aliasing (NOT aliasing the whole table)
- that is dependent on ascending column ordering
- your table has more than three columns (or you could just write it explicitly in the from clause).
It seems like doing that relies on the hazards of regular
t.* stacked with added obscurity. So when is
FROM aliasing useful?