pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedColumn "..." does not exist (view + union)
8+ messages / 5 participants
[nested] [flat]
* Column "..." does not exist (view + union)
@ 2011-12-17 02:57 Stefan Weiss <krewecherl@gmail.com>
2011-12-17 09:02 ` Re: Column "..." does not exist (view + union) Andreas Kretschmer <akretschmer@spamfence.net>
0 siblings, 1 reply; 8+ messages in thread
From: Stefan Weiss @ 2011-12-17 02:57 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Column "..." does not exist (view + union)
2011-12-17 02:57 Column "..." does not exist (view + union) Stefan Weiss <krewecherl@gmail.com>
@ 2011-12-17 09:02 ` Andreas Kretschmer <akretschmer@spamfence.net>
2011-12-17 14:26 ` Re: Column "..." does not exist (view + union) Stefan Weiss <krewecherl@gmail.com>
0 siblings, 1 reply; 8+ messages in thread
From: Andreas Kretschmer @ 2011-12-17 09:02 UTC (permalink / raw)
To: pgsql-sql
Stefan Weiss <krewecherl@gmail.com> wrote:
>
> - 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 reult table doesn't contain a column "ctype", it contains only
"name". Rewrite your query to something like:
select name from (select name, ctype from ... union select name, ctype from ...)
foo order by ctype
Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect. (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly." (unknown)
Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Column "..." does not exist (view + union)
2011-12-17 02:57 Column "..." does not exist (view + union) Stefan Weiss <krewecherl@gmail.com>
2011-12-17 09:02 ` Re: Column "..." does not exist (view + union) Andreas Kretschmer <akretschmer@spamfence.net>
@ 2011-12-17 14:26 ` Stefan Weiss <krewecherl@gmail.com>
2011-12-17 21:36 ` Re: Column "..." does not exist (view + union) Bèrto ëd Sèra <berto.d.sera@gmail.com>
0 siblings, 1 reply; 8+ messages in thread
From: Stefan Weiss @ 2011-12-17 14:26 UTC (permalink / raw)
To: pgsql-sql
On 2011-12-17 10:02, Andreas Kretschmer wrote:
> Stefan Weiss <krewecherl@gmail.com> wrote:
>>
>> 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 reult table doesn't contain a column "ctype", it contains only
> "name". [...]
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.
Thanks, that explains it.
- stefan
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Column "..." does not exist (view + union)
2011-12-17 02:57 Column "..." does not exist (view + union) Stefan Weiss <krewecherl@gmail.com>
2011-12-17 09:02 ` Re: Column "..." does not exist (view + union) Andreas Kretschmer <akretschmer@spamfence.net>
2011-12-17 14:26 ` Re: Column "..." does not exist (view + union) Stefan Weiss <krewecherl@gmail.com>
@ 2011-12-17 21:36 ` Bèrto ëd Sèra <berto.d.sera@gmail.com>
2011-12-17 22:21 ` Re: Column "..." does not exist (view + union) Stefan Weiss <krewecherl@gmail.com>
0 siblings, 1 reply; 8+ messages in thread
From: Bèrto ëd Sèra @ 2011-12-17 21:36 UTC (permalink / raw)
To: Stefan Weiss <krewecherl@gmail.com>; +Cc: pgsql-sql
Hi,
>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.
Bèrto
--
==============================
If Pac-Man had affected us as kids, we'd all be running around in a
darkened room munching pills and listening to repetitive music.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Column "..." does not exist (view + union)
2011-12-17 02:57 Column "..." does not exist (view + union) Stefan Weiss <krewecherl@gmail.com>
2011-12-17 09:02 ` Re: Column "..." does not exist (view + union) Andreas Kretschmer <akretschmer@spamfence.net>
2011-12-17 14:26 ` Re: Column "..." does not exist (view + union) Stefan Weiss <krewecherl@gmail.com>
2011-12-17 21:36 ` Re: Column "..." does not exist (view + union) Bèrto ëd Sèra <berto.d.sera@gmail.com>
@ 2011-12-17 22:21 ` Stefan Weiss <krewecherl@gmail.com>
2011-12-17 23:15 ` Re: Column "..." does not exist (view + union) Bèrto ëd Sèra <berto.d.sera@gmail.com>
2011-12-17 23:38 ` Re: Column "..." does not exist (view + union) Adrian Klaver <adrian.klaver@gmail.com>
2011-12-18 02:58 ` Re: Column "..." does not exist (view + union) Brent Dombrowski <brent.dombrowski@gmail.com>
0 siblings, 3 replies; 8+ messages in thread
From: Stefan Weiss @ 2011-12-17 22:21 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Column "..." does not exist (view + union)
2011-12-17 02:57 Column "..." does not exist (view + union) Stefan Weiss <krewecherl@gmail.com>
2011-12-17 09:02 ` Re: Column "..." does not exist (view + union) Andreas Kretschmer <akretschmer@spamfence.net>
2011-12-17 14:26 ` Re: Column "..." does not exist (view + union) Stefan Weiss <krewecherl@gmail.com>
2011-12-17 21:36 ` Re: Column "..." does not exist (view + union) Bèrto ëd Sèra <berto.d.sera@gmail.com>
2011-12-17 22:21 ` Re: Column "..." does not exist (view + union) Stefan Weiss <krewecherl@gmail.com>
@ 2011-12-17 23:15 ` Bèrto ëd Sèra <berto.d.sera@gmail.com>
2 siblings, 0 replies; 8+ messages in thread
From: Bèrto ëd Sèra @ 2011-12-17 23:15 UTC (permalink / raw)
To: Stefan Weiss <krewecherl@gmail.com>; +Cc: pgsql-sql
Hi
> For normal (non-union) queries, this is possible:
>
Yes, you are correct. My bad.
Bèrto
--
==============================
If Pac-Man had affected us as kids, we'd all be running around in a
darkened room munching pills and listening to repetitive music.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Column "..." does not exist (view + union)
2011-12-17 02:57 Column "..." does not exist (view + union) Stefan Weiss <krewecherl@gmail.com>
2011-12-17 09:02 ` Re: Column "..." does not exist (view + union) Andreas Kretschmer <akretschmer@spamfence.net>
2011-12-17 14:26 ` Re: Column "..." does not exist (view + union) Stefan Weiss <krewecherl@gmail.com>
2011-12-17 21:36 ` Re: Column "..." does not exist (view + union) Bèrto ëd Sèra <berto.d.sera@gmail.com>
2011-12-17 22:21 ` Re: Column "..." does not exist (view + union) Stefan Weiss <krewecherl@gmail.com>
@ 2011-12-17 23:38 ` Adrian Klaver <adrian.klaver@gmail.com>
2 siblings, 0 replies; 8+ messages in thread
From: Adrian Klaver @ 2011-12-17 23:38 UTC (permalink / raw)
To: pgsql-sql; +Cc: Stefan Weiss <krewecherl@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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Column "..." does not exist (view + union)
2011-12-17 02:57 Column "..." does not exist (view + union) Stefan Weiss <krewecherl@gmail.com>
2011-12-17 09:02 ` Re: Column "..." does not exist (view + union) Andreas Kretschmer <akretschmer@spamfence.net>
2011-12-17 14:26 ` Re: Column "..." does not exist (view + union) Stefan Weiss <krewecherl@gmail.com>
2011-12-17 21:36 ` Re: Column "..." does not exist (view + union) Bèrto ëd Sèra <berto.d.sera@gmail.com>
2011-12-17 22:21 ` Re: Column "..." does not exist (view + union) Stefan Weiss <krewecherl@gmail.com>
@ 2011-12-18 02:58 ` Brent Dombrowski <brent.dombrowski@gmail.com>
2 siblings, 0 replies; 8+ messages in thread
From: Brent Dombrowski @ 2011-12-18 02:58 UTC (permalink / raw)
To: Stefan Weiss <krewecherl@gmail.com>; +Cc: pgsql-sql
On Dec 17, 2011, at 2:21 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.
The real problem here is the order of operations. This is what Postgres did:
SELECT * FROM (
SELECT relname
FROM pg_class
WHERE relhasindex
UNION
SELECT relname
FROM pg_class
WHERE relhasoids
) as foo
ORDER BY relpages;
It applied the union before the order by. After the union, the relpages column was projected away. Thus relpages does not exist for the order by. The postgres manual states that this is what will happen.
The UNION clause has this general form:
select_statement UNION [ ALL ] select_statement
select_statement is any SELECT statement without an ORDER BY, LIMIT, FOR UPDATE, or FOR SHARE clause. (ORDER BY and LIMIT can be attached to a subexpression if it is enclosed in parentheses. Without parentheses, these clauses will be taken to apply to the result of the UNION, not to its right-hand input expression.)
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2011-12-18 02:58 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2011-12-17 02:57 Column "..." does not exist (view + union) Stefan Weiss <krewecherl@gmail.com>
2011-12-17 09:02 ` Andreas Kretschmer <akretschmer@spamfence.net>
2011-12-17 14:26 ` Stefan Weiss <krewecherl@gmail.com>
2011-12-17 21:36 ` Bèrto ëd Sèra <berto.d.sera@gmail.com>
2011-12-17 22:21 ` Stefan Weiss <krewecherl@gmail.com>
2011-12-17 23:15 ` Bèrto ëd Sèra <berto.d.sera@gmail.com>
2011-12-17 23:38 ` Adrian Klaver <adrian.klaver@gmail.com>
2011-12-18 02:58 ` Brent Dombrowski <brent.dombrowski@gmail.com>
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