The following bug has been logged online:

Bug reference: 1540
Logged by: Richard Neill
Email address: postgresql (AT) richardneill (DOT) org
PostgreSQL version: 8.01
Operating system: Linux
Description: Enhancement request: 'ambiguous' column reference in
psql
Details:

Dear Postgresql team,

I have a small suggestion, which isn't quite a bug, but where psql throws an
error which it could in principle recover from. These occur when a column
reference is ambiguous, but isn't really, because of information supplied in
the join.


Here is an example, which I just tested in 8.01.


These are the database tables:
----------------------------------
tbl_instruments:
instrument character varying
priceband smallint

----------------------------------
tbl_prices:
priceband smallint
pounds double precision
-----------------------------------


This query fails:
------------------------------------
SELECT instrument,priceband,pounds FROM tbl_instruments,tbl_prices WHERE
tbl_instruments.priceband=tbl_prices.priceband;

ERROR: column reference "priceband" is ambiguous
-----------------------------------



This query succeeds:
-----------------------------------------
SELECT instrument,tbl_instruments.priceband,pounds FROM
tbl_instruments,tbl_prices WHERE
tbl_instruments.priceband=tbl_prices.priceband;
------------------------------------------


I think that the first query ought to succeed, since although priceband is
ambiguous (it could mean either tbl_prices.priceband or
tbl_instruments.priceband), the information in the WHERE clause means that
they are explicitly equal, and so it doesn't matter which one we use.


Thank you very much for all your work - Postgresql is really useful to me.

Richard

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

"Richard Neill" <postgresql (AT) richardneill (DOT) org> writes:
Doing that would be contrary to the SQL specification, AFAICS.

However, you can get the effect you want by writing the query like

SELECT instrument,priceband,pounds FROM
tbl_instruments JOIN tbl_prices USING (priceband);

which both provides the join condition and logically merges the two
input columns into just one output column.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo (AT) postgresql (DOT) org so that your
message can get through to the mailing list cleanly