Resolving an ambiguous column in a subselect

Question

I'm having trouble selecting a column from a subselect that has a shared name between two joined tables using the ON join expression syntax.

I have two tables, event and geography each of which has a geography_id column, which is the same data type, and event.geography_id is a foreign key into geography (geography provides information about an event):

The problem I'm having is that I can't reference the shared column between these two tables when joining them using the ON syntax, but it works when using the USING syntax.

I realize that USING works because it suppresses redundant columns, but since the statement uses many different joined tables with schemas that change more often than not, I would rather be as explicit as possible.

The specific SQL I'm having trouble with is:

select
    x.event_id
from (
    select * from event e
    left join geography g on (e.geography_id = g.geography_id)
) x
where
    x.geography_id in (1,2,3)

Which gives the error:

ERROR: column reference "geography_id" is ambiguous

LINE 8: x.geography_id in (1,2,3)

I'm using PostgreSQL 9.0.14.

Answer 1

It would be a very helpful feature in SQL to be able to select all columns except one or more that you explicitly want to exclude. If it existed, you could have used such a feature to solve your problem by excluding g.geography_id. Unfortunately, such a feature does not appear to exist anywhere, in any DBMS. See https://dba.stackexchange.com/questions/1957/sql-select-all-columns-except-some.

One solution, as @a_horse_with_no_name commented, is to list every single column you want to select, and just omit the ones you don't.

There is actually another, probably preferable, solution, which is to select * and also e.geography_id, but alias the latter to another name that will then be unambiguous in the subquery result-set. Something like this:

select
    x.event_id
from (
    select *, e.geography_id geography_id1 from event e
    left join geography g on (e.geography_id = g.geography_id)
) x
where
    x.geography_id1 in (1,2,3)