How to address field names in a join query

On Mon, 26 Nov 2001, Denis Gasparin wrote:

I have 3 tables with some fields with the same name. For example:

TABLE_A: PR_CODE, DESCRIPTION, IS_VALID

TABLE_B: PR_ST_CODE, PR_CODE, DESCRIPTION

TABLE_C: PR_VD_CODE, PR_CODE, DESCRIPTION

As you see, the DESCRIPTION field is present in all tables.
Now suppose i want to do query like this:

select * from (table_a a inner join table_b b on a.pr_code = b.pr_code) x
inner join table_c c on x.pr_code = c.pr_code

I obtain a resultset with these fields:
PR_CODE | DESCRIPTION | IS_VALID | PR_ST_CODE | DESCRIPTION | PR_VD_CODE |
DESCRIPTION

The question is: how can i refer to the fields DESCRIPTION of each table?

I can refer to table_c DESCRIPTION with c.DESCRIPTION but if i write
x.DESCRIPTION postgresql cannot identy the true field names because
actually there are two x.DESCRIPTION fields... How can i refer to these?

At least on current sources (don't have 7.1 here to test) you can give x
a column list (in position order) which allows you to rename columns from
the first join so you can rename the descriptions to something like
A_Description and B_Description.

Denis Gasparin <denis@edistar.com> writes:

The question is: how can i refer to the fields DESCRIPTION of each table?

Either leave off the join's alias, or extend it to rename the columns as
well as the joined table. Aliasing hides the table names that were
inside the join, so you're stuck if you haven't renamed the columns.
Example:

create table int8_tbl (q1 int8, q2 int8);

select a.q1,b.q2 from
(int8_tbl a inner join int8_tbl b on a.q1=b.q1);

select a.q1,b.q2 from
(int8_tbl a inner join int8_tbl b on a.q1=b.q1) x; -- WRONG

select aq1, x.bq2 from
(int8_tbl a inner join int8_tbl b on a.q1=b.q1) x(aq1,aq2,bq1,bq2);

I suspect that the first variant may be illegal per the letter of the
SQL spec (since the joined table has duplicate column names), but
Postgres doesn't enforce any such restriction.

regards, tom lane