Tom Lane wrote: > Michael Glaesemann <[EMAIL PROTECTED]> writes: > > On Dec 26, 2006, at 18:39 , Mike Benoit wrote: > >> ERROR: ORDER BY on a UNION/INTERSECT/EXCEPT result must be on one of > >> the result columns > > > Even though state is a column in both tables, the order by is using > > an expression, rather than a column. > > ... > > I'm not sure of the underlying reasons why your query doesn't work, > > but give these a shot. > > There are some implementation reasons for not supporting expressions > computed on a UNION result without an intervening sub-SELECT. It's too > late at night for me to recall exactly what they are :-( --- one is that > an Append plan node doesn't do any expression evaluation, but I think > there are some more-subtle issues too. Suffice it to say that we could > support this if we wanted to throw enough effort at it, but so far other > problems have seemed more pressing. > > In the meantime, it seems like the above-quoted error message is not > clear enough, since Mike failed to get the point that "the ORDER BY > item has to be just a simple column name of the UNION output". Anyone > have a suggestion for better wording?
I have updated the wording from "ORDER BY on a UNION/INTERSECT/EXCEPT result must be on one of the result columns"))); to: "ORDER BY on a UNION/INTERSECT/EXCEPT result must match existing result columns"))); The 'match' wording might help, rather then 'use'. -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---------------------------(end of broadcast)--------------------------- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
