PostgreSQL – When COLUMN Aliases in FROM Clauses Are Needed

aliaspostgresql

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?

Best Answer

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.

Related Question