From: Stefan Weiss <krewecherl@gmail.com>
To: pgsql-sql@postgresql.org
Subject: Column "..." does not exist (view + union)
Date: Sat, 17 Dec 2011 03:57:55 +0100
Message-ID: <4EEC0533.9050007@gmail.com> (raw)
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
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: <4EEC0533.9050007@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