From: Adrian Klaver <adrian.klaver@gmail.com>
To: pgsql-sql@postgresql.org
Subject: Re: Column "..." does not exist (view + union)
Date: Sat, 17 Dec 2011 15:38:43 -0800
User-Agent: KMail/1.13.5 (Linux/2.6.32-36-generic; KDE/4.4.5; i686; ; )
Cc: Stefan Weiss <krewecherl@gmail.com>
References: <4EEC0533.9050007@gmail.com>
	<CAKwGa_9D9M6V+bYJwayx_JtD1Tq3oQ0vouSXSshebQ8bbiJGMg@mail.gmail.com>
	<4EED15EA.6000507@gmail.com>
In-Reply-To: <4EED15EA.6000507@gmail.com>
MIME-Version: 1.0
Content-Type: Text/Plain;
  charset="utf-8"
Content-Transfer-Encoding: 7bit
Message-Id: <201112171538.43512.adrian.klaver@gmail.com>

On Saturday, December 17, 2011 2:21:30 pm Stefan Weiss wrote:

> 
> 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.


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


> 
> 
> thanks,
> stefan

-- 
Adrian Klaver
adrian.klaver@gmail.com