Skip site navigation (1) Skip section navigation (2)

Re: BUG #1540: Enhancement request: 'ambiguous' column reference in psql

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: "Richard Neill" <postgresql(at)richardneill(dot)org>
Cc: pgsql-bugs(at)postgresql(dot)org
Subject: Re: BUG #1540: Enhancement request: 'ambiguous' column reference in psql
Date: 2005-03-14 05:06:37
Message-ID: 4912.1110776797@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-bugs
"Richard Neill" <postgresql(at)richardneill(dot)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

In response to

Responses

pgsql-bugs by date

Next:From: Tom LaneDate: 2005-03-14 05:09:25
Subject: Re: BUG #1539: Suggestion
Previous:From: Michael FuhrDate: 2005-03-14 05:00:14
Subject: Re: BUG #1537: alter table statement

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group