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
It seems like doing that relies on the hazards of regular
t.* stacked with added obscurity. So when is
FROM aliasing useful?
PostgreSQL gives them default names, but that's besides the point. You can't alias them in a COLUMN list that doesn't exist.
SELECT t.* FROM (VALUES ('row1',1), ('row2',2)) AS t;
That would be a great example of the VALUES LIST syntax which requires you to alias in
SELECT t.* FROM (VALUES ('row1',1), ('row2',2)) AS t(english,rownum);
From there you can even use the alias in the select list.
SELECT rownum FROM (VALUES ('row1',1), ('row2',2)) AS t(english,rownum);
And, that brings us to a complex example like this one found on page 190 of PostGIS in Action, Second Edition.
SELECT oid --**only possible because of FROM ALIASING.** , lowrite(lo_open(oid, 131072), img) As num_bytes FROM ( VALUES ( lo_create(0), ( SELECT ST_AsGDALRaster( ST_Band(rast,1) , 'USGSDEM' , ARRAY[ 'PRODUCER=' || quote_literal('postgis_in_action') , 'INTERNALNAME=' || quote_literal(rast_name) ] ) As dem FROM ch07.bag_o_rasters WHERE rast_name = 'Raster 1 band heatmap' ) ) ) As v(oid,img); --**FROM ALIASING**
If that's hard to swallow there is one other awkward construct there and that's a SELECT as a COLUMN.
SELECT ( SELECT 1 ), ( SELECT 2 ); -- one row "1,2" SELECT lo_create(0), ( SELECT 2 ); -- one row "oid,2"
lo_create returns an oid. That's beyond the scope of this answer though.
For another real world example, see this question I just answered.
This may be worth mentioning since it is in the docs,
SELECT * FROM temp; foo | bar -----+-------- 1 | evan 2 | dba.se (2 rows)
And you self-join you'll get two
SELECT * FROM temp JOIN temp AS t2 USING (foo); foo | bar | bar -----+--------+-------- 1 | evan | evan 2 | dba.se | dba.se (2 rows)
But, with FROM aliasing you can label them separately,
SELECT * FROM temp JOIN temp AS t2(foo,bar2) USING (foo); foo | bar | bar2 -----+--------+-------- 1 | evan | evan 2 | dba.se | dba.se (2 rows)
However, that's not really better than the COLUMN-aliasing method,
SELECT foo, temp.bar, t2.bar AS bar2 FROM temp JOIN temp AS t2 USING (foo);
The only difference is that uniquely in the FROM-aliasing method you refer to all instance of