Re: ORDER BY col is NULL in UNION causes error? - Mailing list pgsql-general

Tom Lane wrote:
> Bruce Momjian <bruce@momjian.us> writes:
> > Well, it can be an expression _if_ the expression _matches_ an existing
> > UNION column.
>
> You're mistaken.  It has to be *an output column name*.  Not anything else.

Yea, I was thinking of this:

    SELECT *, state IS NULL AS state_is_null
    FROM income_tax_rate_us
    UNION
    SELECT *, state IS NULL AS state_is_null
    FROM income_tax_rate_us
    ORDER BY state_is_null

Here the column result is an expression, and you reference that.
Updated wording:

 test=> select * from test union select * from test order by x is null;
 ERROR:  A UNION/INTERSECT/EXCEPT ORDER BY cannot use expressions or functions, only result column names

--
  Bruce Momjian   bruce@momjian.us
  EnterpriseDB    http://www.enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +