Thread: How to alias table columns in result?

Hi,

If I have:
1.) table car with columns index and name
2.) table driver with columns index, name and car_index

and query:
SELECT d.*, c.* FROM driver as d LEFT OUTER JOIN car AS c ON
d.car_index=c.index;

How can I get results that have distinct names for columns (ex.
d.name, d.index, c.name, c.index,...)?

tnx
boris

Quoting nori <kodmasin@gmail.com>:

> Hi,
> 
> If I have:
> 1.) table car with columns index and name 
> 2.) table driver with columns index, name and car_index
> 
> and query:
> SELECT d.*, c.* FROM driver as d LEFT OUTER JOIN car AS c ON
> d.car_index=c.index;
> 
> How can I get results that have distinct names for columns (ex.
> d.name, d.index, c.name, c.index,...)?

Here's where you have to get explicit; d.* won't work.
If you want to have names with (.) in them, they have to be quoted, too.
Me, I'd use "d_name, d_index, c_name, ..."

SELECT d.name as "d.name",      d.index as "d.index",      c.name as "c.name",      ...

Thanks

Sorry, my question was missing one important detail. My tables have
quite a lot columns (which unfortunately have same names in both
tables) so is it possible to do same as below but without specifying
alias for each column. Now my queries are long and they do not look
nice.

boris

On 8/11/05, Mischa Sandberg <mischa.sandberg@telus.net> wrote:
> SELECT d.name as "d.name",
>        d.index as "d.index",
>        c.name as "c.name",
>        ...

Yes - just alias the columns you need to alias

Nick 

-----Original Message-----
From: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-owner@postgresql.org]
On Behalf Of nori
Sent: 11 August 2005 10:48
To: Mischa Sandberg
Cc: pgsql-sql@postgresql.org
Subject: Re: [SQL] How to alias table columns in result?

Thanks

Sorry, my question was missing one important detail. My tables have quite a
lot columns (which unfortunately have same names in both
tables) so is it possible to do same as below but without specifying alias
for each column. Now my queries are long and they do not look nice.

boris

On 8/11/05, Mischa Sandberg <mischa.sandberg@telus.net> wrote:
> SELECT d.name as "d.name",
>        d.index as "d.index",
>        c.name as "c.name",
>        ...

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Ok I got it. There is no way to alias all columns of some table with
some "prefix" that will be visible in result except to alias each
column.

Tnx Nick, Micsha
P.S. sorry for my bad english

On 8/11/05, Nick Stone <nick@harelane.com> wrote:
> Yes - just alias the columns you need to alias
>
> Nick
>

nori wrote:
> Ok I got it. There is no way to alias all columns of some table with
> some "prefix" that will be visible in result except to alias each
> column.

Only other way would be to write a view for each table, then write all
your queries against the views.