SEO

The following bug has been logged online:

Bug reference: 1540
Logged by: Richard Neill
Email address: postgresql@richardneill.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 wrote:
> 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.

Well, it just means the type's equality operator returns true for these
two values -- I'm not sure it is wise to assume they are completely
interchangeable.

More generally, it makes sense to me that resolution of column
references is a property of the syntax of a statement, not something
derived from its semantics (e.g. the fact that we can infer for some
particular statement that two columns are equal).

-Neil

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

"Richard Neill" <postgresql@richardneill.org> writes:
> SELECT instrument,priceband,pounds FROM tbl_instruments,tbl_prices WHERE
> tbl_instruments.priceband=tbl_prices.priceband;

> ERROR: column reference "priceband" is ambiguous

> 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.

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@postgresql.org so that your
message can get through to the mailing list cleanly