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