regression=# select * from int8_tbl union all select * from int8_tbl order by q2; q1 | q2 ------------------+------------------- 4567890123456789 | -4567890123456789 4567890123456789 | -4567890123456789 4567890123456789 | 123 4567890123456789 | 123 123 | 456 123 | 456 4567890123456789 | 4567890123456789 123 | 4567890123456789 4567890123456789 | 4567890123456789 123 | 4567890123456789 (10 rows)
regression=# create table fooey(z1,z2) as select * from int8_tbl union all select * from int8_tbl order by q2; ERROR: column "q2" does not exist LINE 1: ... from int8_tbl union all select * from int8_tbl order by q2; ^ The reason for this behavior is that analyze.c attaches the CREATE TABLE AS aliases to the targetlist (via applyColumnNames) before it processes the ORDER BY clause. (So, "order by z2" works instead.) This seems like a bug: one would expect that the aliases do not change the semantics of the SELECT part of the command. Interestingly, 7.2 seems to get it right, the misbehavior appears in 7.3 and later. I'm inclined to fix this in HEAD but not back-patch it, on the grounds that there might be apps out there expecting the existing behavior, and it's not a big enough deal to change behavior in a minor release. Thoughts? regards, tom lane ---------------------------(end of broadcast)--------------------------- TIP 2: Don't 'kill -9' the postmaster