When are COLUMN aliases in FROM clauses needed?

Question

FROM provides 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 FROM item 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 SELECT must refer to this FROM item as f not 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);

vs

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?

Answer 1

If the columns don't exist (VALUES clause)

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 FROM list.

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.

In a SELF JOIN

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 bar columns.

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 t2.bar as bar2.