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

On 11/24/2010 12:31 PM, Florian Weimer wrote:
> * Grzegorz Jaśkiewicz:
>
>> 2010/11/24 Florian Weimer<fweimer(at)bfk(dot)de>:
>>> * Grzegorz Jaśkiewicz:
>>>
>>>> just never use SELECT *, but always call columns by names. You'll
>>>> avoid having to depend on the order of columns, which is never
>>>> guaranteed, even if the table on disk is one order, the return columns
>>>> could be in some other.
>>> This can't be true because several SQL features rely on deterministic
>>> column order. Here's an example:
>>>
>>> SELECT 1 AS a, 2 AS b UNION SELECT 3 AS b, 4 AS a;
>>>
>>> a | b
>>> ---+---
>>> 1 | 2
>>> 3 | 4
>>> (2 rows)
>> Yes, most DBs do a good job to keep it consistent, but they don't have
>> to. So unless you specify column names explicitly (like you did in the
>> example above), there's no guarantees.
> If the database looked at the column names, the result would be
> (1, 2), (4, 3), not (1, 2), (3, 4).
It seems that UNION does not do what you think it does.
Consider you original example, slightly altered:
# SELECT c1,c2 FROM (SELECT 1 AS c1, 2 AS c2 UNION SELECT 2 AS c2, 1 AS
c1) AS x;
c1 | c2
----+----
1 | 2
2 | 1

If you change a column name in the first SELECT in the UNION:
# SELECT c1,c2 FROM (SELECT 1 AS *c3*, 2 AS c2 UNION SELECT 2 AS c2, 1
AS c1) AS x;
ERROR: column "c1" does not exist
LINE 1: SELECT c1,c2 FROM (SELECT 1 AS c3, 2 AS c2 UNION SELECT 2 AS...
^

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.

Postgresql 8.4.5

/Fredric