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

Re: How to address field names in a join query

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Denis Gasparin <denis(at)edistar(dot)com>
Cc: Postgresql General <pgsql-general(at)postgresql(dot)org>
Subject: Re: How to address field names in a join query
Date: 2001-11-26 15:04:11
Message-ID: 12547.1006787051@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-general
Denis Gasparin <denis(at)edistar(dot)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

In response to

pgsql-general by date

Next:From: kdebisschopDate: 2001-11-26 15:08:03
Subject: Re: pl/perl won't build under 7.1.3
Previous:From: Martín MarquésDate: 2001-11-26 14:59:02
Subject: Re: Rule

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