5

I'm reading through the Postgres documentation, the page on SELECT statements, and I ran across an aspect of aliases that I have never encountered.

In the section on FROM clauses, subheading alias, there is a sentence stating:

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.

There are no examples given in the documentation that I could find.

I know how to set up output names as aliases, but that doesn't appear to be the same thing.


The synopsis for SELECT includes the lines:

... SELECT [ ALL | DISTINCT [ ON (expression[, ...] ) ] ] * |expression[ [ AS ]output_name] [, ...] [ FROMfrom_item[, ...] ] ...

And defines from_item as:

where from_item can be one of:

[ ONLY ]table_name[ * ] [ [ AS ]alias[ (column_alias[, ...] ) ] ] (select) [ AS ]alias[ (column_alias[, ...] ) ] ...(other forms omitted)...

Note that from_item actually includes column_alias.


It makes sense to me that the ( select ) form can be given column aliases in the FROM clause (rather than only output_names, in the "expressions" of the SELECT statement), since the "columns" of the subquery will have been explicitly chosen in most cases and thus the sequence will be known. So I would imagine in that case the column_alias values could simply be a list of names, and they would be matched up in sequence against the columns returned by the subquery. (Though an example would be nice.)

However, how can column aliases be used for a table_name? Do you have to know the exact sequence of columns defined in the table, or can you set an alias just for one or two of these in the FROM clause?

What if you only want to set a column_alias for one column with a very long name (and leave the other columns not aliased); is this possible? (If so, is this Postgres specific?)

7

The column aliases there override the column names/aliases of the internal select subquery (derived table). The same way, they can override the column names of the table (whether it's base table, a view, a derived table or a cte does not matter at all).

So, the simple example will give an error:

select 
    a, count_a              -- invalid here (have been overridden)
from 
    ( select t.a, count(*) as count_a
      from t
      group by t.a
      order by count_a desc           -- count_a is valid here
      limit 8
    ) 
      as d (b, count_b) ;

but this will work:

select 
    b, count_b              -- valid column aliases
from     
    -- identical as above 

the names a and count_a are valid inside the subquery (derived table) but not outside because they have been overridden by b and count_b.

Do you have to know the exact sequence of columns defined in the table, or can you set an alias just for one or two of these in the FROM clause?

Yes, you do have to know the sequence of columns.

But you don't have to change all columns. Say the table has 5 columns. If you use:

select t.*
from table_name as t (a,b,c) ; 

only the first 3 columns will appear with the new names (a,b,c). The 4th and 5th will show with their real names. You'll get an error if you provide more aliases than needed (eg. 6 aliases for a 5-column table).

What if you only want to set a column_alias for one column with a very long name (and leave the other columns not aliased); is this possible? (If so, is this Postgres specific?)

Only if it's the first. Or by providing all the previous column names up to the column you want to alias with a different name.

I suppose you can't just give an alias for the third column only, or something like that?

I don't know of any syntax to allow you to alias only the 3rd column, without providing the names of the 1st and 2nd column.


Overall, the usefulness of the feature is at least debatable when used for base tables. And the above query that overrides the names of just 3 of the possibly many columns reeks obfuscation and could be very well considered bad practice.

But the feature is provided because it's standard SQL and for completeness. It wouldn't make sense to have this only for subqueries and CTEs and not for other kinds of tables.

One case where it can be useful is (not with base table but with) the VALUES construct, where the columns get default names of column1, column2, etc. and this aliasing can be used to select more meaningful names:

select 
    a, b
from 
    ( values
         (1, 2),
         (2, 3), 
         (3, 5)
    ) 
      as d (a, b) ;
  • "I don't know of any syntax to allow you to alias only the 3rd column, without providing the names of the 1st and 2nd column." This surprised me, but now I recognize it as a perfect example of why SQL is not relational. – Wildcard Nov 24 '16 at 12:16
  • @Wildcard I don't follow. What does "relationality" has to dowith it? – ypercubeᵀᴹ Nov 24 '16 at 12:21
  • Nothing, except that intuitively that's how I expected SQL to work, as it makes more sense with regard to the theoretical underpinnings of relational databases. – Wildcard Nov 24 '16 at 18:17
  • To clarify my previous comment: in the Relational Model, the attributes of a relation do not have any sequence; they are a set. For the same reason, there cannot be two attributes with the same name. Both of these are in contrast to SQL. A "rename" in relational algebra only requires the original name of the attribute and the target name. – Wildcard May 3 '17 at 0:34
  • I agree on the first part (that in SQL tables may not have 2 identically named columns but results can have and that is in contrast with the relational model). But the lack of a "rename" operator - that renames a single column - is only a minor disagreement (between SQL and the RM). – ypercubeᵀᴹ May 3 '17 at 10:55

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.