From: | Fredric Fredricson <Fredric(dot)Fredricson(at)bonetmail(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: alter table add column - specify where the column will go? |
Date: | 2010-11-24 13:12:37 |
Message-ID: | 4CED0F45.10901@bonetmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
Attachment | Content-Type | Size |
---|---|---|
Fredric_Fredricson.vcf | text/x-vcard | 217 bytes |
From | Date | Subject | |
---|---|---|---|
Next Message | Daniel Verite | 2010-11-24 14:54:21 | Re: alter table add column - specify where the column will go? |
Previous Message | Dave Page | 2010-11-24 11:46:44 | Just 2 days left to register for PGDay.EU 2010 |