On 11/24/2010 12:31 PM, Florian Weimer wrote:
It seems that UNION does not do what you think it does.* Grzegorz JaÅkiewicz:2010/11/24 Florian Weimer <fweimer@xxxxxx>:* 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). 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 |
begin:vcard fn:Fredric Fredricson n:Fredricson;Fredric org:Ln4 Solutions AB email;internet:Fredric.Fredricson@xxxxxxxxxxxxx title:CTO tel;home:+46 8 91 64 39 tel;cell:+46 70 677 58 48 version:2.1 end:vcard
-- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
