PostgreSQL SQL - How to alias table columns in result?

Author

How to alias table columns in result?

nori

2005-08-10, 8:25 pm

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

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

Mischa Sandberg

2005-08-10, 8:25 pm

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",
...

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

nori

2005-08-11, 7:24 am

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

Nick Stone

2005-08-11, 7:24 am

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

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql
.org so that your
message can get through to the mailing list cleanly

nori

2005-08-11, 7:24 am

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
>

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

Jeff Boes

2005-08-11, 1:27 pm

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.

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

Sponsored Links