Re: Column "..." does not exist (view + union)

On 2011-12-17 22:36, Bèrto ëd Sèra wrote:
>>I see. So this has to do with the union; after combining the two
>>queries, the tables from the FROM clauses are no longer available.
>
> this has nothing to do with the UNION, but with the fact that the result
> set is ordered after being produced, so you can order by any of its
> elements, and only by that. You can actually order by calling them
> acording to their position in the result set, like in:
>
> SELECT
> relname,
> relpages
> FROM pg_class
> ORDER BY 1;
>
> where 1 is actually the first element (no matter how it's called). The
> table as such is never available to ORDER BY, no matter how simple your
> query is.

I know, but the problem only occurs when I want to sort by a column
which hasn't been selected, and thus cannot be referred to by its index.
For normal (non-union) queries, this is possible:

SELECT relname
FROM pg_class
WHERE relhasindex
ORDER BY relpages;

In this trivial case, PostgreSQL knows where to look for "relpages".
Not so in a union:

SELECT relname
FROM pg_class
WHERE relhasindex
UNION
SELECT relname
FROM pg_class
WHERE relhasoids
ORDER BY relpages;

(ERROR: column "relpages" does not exist)

I understand the error now (I think), and I know how to avoid it.

thanks,
stefan