From: Stefan Weiss <krewecherl@gmail.com>
To: pgsql-sql@postgresql.org
Subject: Re: Column "..." does not exist (view + union)
Date: Sat, 17 Dec 2011 23:21:30 +0100
Message-ID: <4EED15EA.6000507@gmail.com> (raw)
In-Reply-To: <CAKwGa_9D9M6V+bYJwayx_JtD1Tq3oQ0vouSXSshebQ8bbiJGMg@mail.gmail.com>
References: <4EEC0533.9050007@gmail.com>
<20111217090246.GA12209@tux>
<4EECA699.2020700@gmail.com>
<CAKwGa_9D9M6V+bYJwayx_JtD1Tq3oQ0vouSXSshebQ8bbiJGMg@mail.gmail.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: krewecherl@gmail.com
Subject: Re: Column "..." does not exist (view + union)
In-Reply-To: <4EED15EA.6000507@gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox