Re: alter table add column - specify where the column will go?

On 2010-11-24, Daniel Verite <daniel(at)manitou-mail(dot)org> wrote:
> Fredric Fredricson wrote:
>
>> But if you change the column names in the second SELECT in the UNION this is
>> ignored:
>> # SELECT c1,c2 FROM (SELECT 1 AS c1, 2 AS c2 UNION SELECT 2 AS c3, 1 AS c4)
>> AS x;
>> c1 | c2
>> ----+----
>> 1 | 2
>> 2 | 1
>> Apparently, in a UNION the column names are derived from the first statement
>> only.
>
> The example upthread demonstrates that in certain contexts, column positions
> are relevant whereas column names are not. The modified query you show here
> doesn't lead to any different conclusion.
>
> The allegation that row.* doesn't come with a deterministic column order
> remains pretty much unsubstantiated at this point.

It's deterministic, just subject to change (eg if the table is
re-created with a different order, or if a column is dropped and
re-added)

I try to always use column and table names when dealing with tables
other people could mess with.

If dealing with a temp table I sometimes take shortcuts as the source
is all in one place, so anyone messing with it will hopefully be aware
of the consequences of their actions.

--
⚂⚃ 100% natural