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).
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
FROMclause?
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.