Message-ID: <4EEC0533.9050007@gmail.com>
Date: Sat, 17 Dec 2011 03:57:55 +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: Column "..." does not exist (view + union)
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 7bit

Assuming the following simple setup with two data tables, one mapping
table, and one view -

-- ----------------------------------------------------------------
CREATE TABLE dossier (
    id          SERIAL   NOT NULL PRIMARY KEY
);
CREATE TABLE contact (
    id          SERIAL   NOT NULL PRIMARY KEY,
    name        TEXT     NOT NULL,
    firstname   TEXT     NULL
);
CREATE TABLE dossier_contact (
    dossier_id  INTEGER  NOT NULL REFERENCES dossier(id),
    contact_id  INTEGER  NOT NULL REFERENCES contact(id),
    ctype       INTEGER  NOT NULL,
    PRIMARY KEY (dossier_id, contact_id)
);
CREATE VIEW dossier_contact_v AS
    SELECT  dc.dossier_id,
            dc.contact_id,
            dc.ctype,
            (CASE WHEN c.firstname IS NOT NULL
                  THEN c.name || ', ' || c.firstname
                  ELSE c.name
                  END) AS name
      FROM  dossier_contact dc
      JOIN  contact c ON c.id = dc.contact_id;
-- ----------------------------------------------------------------

- running this query -

    SELECT  name
      FROM  dossier_contact_v
     WHERE  dossier_id = 56993
       AND  ctype = 234
UNION
    SELECT  name
      FROM  dossier_contact_v
     WHERE  dossier_id = -1
       AND  ctype = -1
ORDER BY ctype;

- fails with the following error message:

ERROR:  column "ctype" does not exist
LINE 10: ORDER BY ctype;
                  ^

The same query works fine without the ORDER BY, without the UNION, or
when I select the "ctype" column in addition to "name".
Why?

Using an alias in the FROM clause gives a different error:

    SELECT  x.name
      FROM  dossier_contact_v x
     WHERE  x.dossier_id = 56993
       AND  x.ctype = 234
UNION
    SELECT  x.name
      FROM  dossier_contact_v x
     WHERE  x.dossier_id = -1
       AND  x.ctype = -1
ORDER BY x.ctype;

ERROR:  missing FROM-clause entry for table "x"
LINE 10: ORDER BY x.ctype
                  ^

I am using "PostgreSQL 8.4.10 on x86_64-pc-linux-gnu, compiled by GCC
gcc-4.5.real (Ubuntu/Linaro 4.5.2-8ubuntu4) 4.5.2, 64-bit".


thanks,
stefan