Alvaro Herrera wrote:
>>Incidentally when I did that I only got back one row. What's up with that?
>
>
> Try with "union all" instead of plain union.
>
Talk about serendipity. The problem I've been struggling with for the
last few hours has been why my query wasn't producing sorted output even
though I put in an ORDER BY and the EXPLAIN shows that it is ordering.
The DISTINCT implied by the UNION must have been messing up the sorting.
The docs say
(http://www.postgresql.org/docs/7.4/interactive/sql-select.html#SQL-UNION):
select_statement is any SELECT statement without an ORDER BY, LIMIT, or
FOR UPDATE clause. (ORDER BY and LIMIT can be attached to a
subexpression if it is enclosed in parentheses.
... but I *did* put my SELECTs in parentheses. This is either a bug in
pg or a serious ommision from the docs.