From: | Robert Haas <robertmhaas(at)gmail(dot)com> |
---|---|
To: | pgsql-hackers(at)postgresql(dot)org |
Subject: | CTE containing ambiguous columns |
Date: | 2009-11-13 04:20:12 |
Message-ID: | 603c8f070911122020p273aa220hca39b7ee9d886bc8@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
Suppose you do this:
create table animals (id serial primary key, name varchar not null);
Then you can do this:
with beings as (select * from animals) select * from beings where id = 1;
But not this:
with beings as (select * from animals a1, animals a2) select * from
beings where id = 1;
Because:
ERROR: column reference "id" is ambiguous at character 82
STATEMENT: with beings as (select * from animals a1, animals a2)
select * from beings where id = 1;
ERROR: column reference "id" is ambiguous
LINE 1: ...m animals a1, animals a2) select * from beings where id = 1;
^
My email program will probably mangle this, so the error cursor here
is point to "id = 1", at the end, and saying that's ambiguous. Which
is sorta kinda true, but the usual remedy of qualifying it with a
relation name (here, beings.id) fails. And you can't quantify it with
a1.id or a2.id either, they're out of scope. In some sense, the real
problem is with "select *", because that is what is expanding into a
non-unique list of column names. But you don't actually trigger an
error unless you try to reference one; the same query works fine
without the where clause.
I'm not sure if there's anything useful we can do about this, but it
definitely threw me for a loop.
...Robert
From | Date | Subject | |
---|---|---|---|
Next Message | Bruce Momjian | 2009-11-13 04:31:33 | Re: next CommitFest |
Previous Message | KaiGai Kohei | 2009-11-13 03:46:57 | Re: TRIGGER with WHEN clause |