Message-ID: <4EED15EA.6000507@gmail.com>
Date: Sat, 17 Dec 2011 23:21:30 +0100
From: Stefan Weiss <krewecherl@gmail.com>
User-Agent: Mozilla/5.0 (X11; U; Linux x86_64; en-US;
	rv:1.9.2.23) Gecko/20110922 Thunderbird/3.1.15
MIME-Version: 1.0
To: pgsql-sql@postgresql.org
Subject: Re: Column "..." does not exist (view + union)
References: <4EEC0533.9050007@gmail.com>	<20111217090246.GA12209@tux>
	<4EECA699.2020700@gmail.com>
	<CAKwGa_9D9M6V+bYJwayx_JtD1Tq3oQ0vouSXSshebQ8bbiJGMg@mail.gmail.com>
In-Reply-To: 
 <CAKwGa_9D9M6V+bYJwayx_JtD1Tq3oQ0vouSXSshebQ8bbiJGMg@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit

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