6

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?

10

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.

  • 1
    @ypercubeᵀᴹ for real terror, SELECT "?column?" AS f FROM (SELECT 1) AS t – Evan Carroll Nov 17 '16 at 17:09
  • Interesting; so it seems that column aliases in from clauses are never actually needed but may (arguably) be more expressive in some cases. – Wildcard Nov 17 '16 at 18:47
  • UNNESTing an array is another example. – jpmc26 Nov 17 '16 at 22:06
  • @Wildcard that's not safe even if true The column names are not specified by the SQL standard and different database systems do it differently, We're just kind of poking fun at a cool PG-trick. – Evan Carroll Nov 17 '16 at 23:40
  • 1
    Ahh, but I'm talking about column aliases for a table name, not for a subselect or a WITH clause. A values call is actually a subselect, not a table. See the two formats for from_item which I list in my question, quoting from the docs. Your comment above applies to the second form (subselects) but not to the first. So my earlier comment could be rephrased: It seems that table_names specified in a FROM clause never need to have column_aliases, as the same effect could always be achieved by using output_names. That suit you? :) – Wildcard Nov 17 '16 at 23:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.