Ben Cornwell

Most people working with postgres have probably encountered something like this before:

SELECT
instrument,
priceband,
pounds
FROM   tbl_instruments,
tbl_prices
WHERE   tbl_instruments.priceband=tbl_prices.priceband;
ERROR:  column reference "priceband" is ambiguous

Although that’s normal behavior according to the SQL standard, and very easy to fix using ‘tbl_instruments.priceband’ in the SELECT, it kind-of doesn’t make sense given that WHERE clause: both tbl_instruments.priceband and tbl_prices.priceband will yeild the same value. I found the following in the Postgres mailing archives (imo, a much neater solution than qualifying the column names):

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

> BTW, the cute way to do that is
> 	BEGIN;
> 	DROP INDEX unwanted;
> 	EXPLAIN ANALYZE whatever...;
> 	ROLLBACK;
>
> No need to actually rebuild the index when you are done.
>
> This does hold an exclusive lock on the table for the duration of your
> experiment, so maybe not such a good idea in a live environment ... but
> then again, dropping useful indexes in a live environment isn't a good
> idea either, and this at least reduces the duration of the experiment by
> a good deal.

I found this comment by Tom Lane in the Postgresql mailing list archive yesterday. A pretty nifty trick, and perhaps something useful for inclusion in a university syllabus?