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, in the simple example:

    select 
        b, count_b            -- valid column aliases
        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) ;

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

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) ;