[GENERAL] wildcard alias

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|

[GENERAL] wildcard alias

Matthew Terenzio

I suppose I should have named things differently but is there a way to
join two tables with a bunch of identical column names and rather than
explicitly alias each column just use some sort of wildcard like:

SELECT tablename.* AS  alias.*

OR do I have to name each column like:

columnname as alias

Thanks much.


---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
       choose an index scan if your joining column's datatypes do not
       match
Reply | Threaded
Open this post in threaded view
|

Re: [GENERAL] wildcard alias

Martijn van Oosterhout
I don't know what your query or aliases have to do with joins, but if
you want to join two tables on all fields with the same name, you can
do a NATURAL JOIN.

Have a nice day,

On Fri, Nov 10, 2006 at 05:17:12PM -0500, Matthew Terenzio wrote:

>
> I suppose I should have named things differently but is there a way to
> join two tables with a bunch of identical column names and rather than
> explicitly alias each column just use some sort of wildcard like:
>
> SELECT tablename.* AS  alias.*
>
> OR do I have to name each column like:
>
> columnname as alias
>
> Thanks much.
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
>       choose an index scan if your joining column's datatypes do not
>       match
--
Martijn van Oosterhout   <[hidden email]>   http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.

signature.asc (196 bytes) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: [GENERAL] wildcard alias

Joshua Drake-2
On Fri, 2006-11-10 at 23:34 +0100, Martijn van Oosterhout wrote:

> I don't know what your query or aliases have to do with joins, but if
> you want to join two tables on all fields with the same name, you can
> do a NATURAL JOIN.
>
> Have a nice day,
>
> On Fri, Nov 10, 2006 at 05:17:12PM -0500, Matthew Terenzio wrote:
> >
> > I suppose I should have named things differently but is there a way to
> > join two tables with a bunch of identical column names and rather than
> > explicitly alias each column just use some sort of wildcard like:
> >
> > SELECT tablename.* AS  alias.*
> >
> > OR do I have to name each column like:
> >
> > columnname as alias
> >

Yes: SELECT foo as bar from baz;

Sincerely,

Joshua D. Drake



> > Thanks much.
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 9: In versions below 8.0, the planner will ignore your desire to
> >       choose an index scan if your joining column's datatypes do not
> >       match
>
--

      === The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive  PostgreSQL solutions since 1997
             http://www.commandprompt.com/

Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate




---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
       subscribe-nomail command to [hidden email] so that your
       message can get through to the mailing list cleanly
Reply | Threaded
Open this post in threaded view
|

Re: [GENERAL] wildcard alias

Dawid Kuroczko
In reply to this post by Matthew Terenzio
On 11/10/06, Matthew Terenzio <[hidden email]> wrote:
>
> I suppose I should have named things differently but is there a way to
> join two tables with a bunch of identical column names and rather than
> explicitly alias each column just use some sort of wildcard like:
>
> SELECT tablename.* AS  alias.*

Well:
qnex=# CREATE TABLE tab1 (a int, b int);
qnex=# CREATE TABLE tab2 (a int, b int);
qnex=# INSERT INTO tab1 VALUES(1,2);
qnex=# INSERT INTO tab1 VALUES(3,4);
qnex=# INSERT INTO tab2 VALUES(1,7);
qnex=# INSERT INTO tab2 VALUES(3,12);

And you want to, instread of:

qnex=# SELECT * FROM tab1 JOIN tab2 USING(a);
 a | b | b
---+---+----
 1 | 2 |  7
 3 | 4 | 12

Do something like:

qnex=# SELECT tab1.*, alias.* FROM tab1 JOIN tab2 alias USING(a);
 a | b | a | b
---+---+---+----
 1 | 2 | 1 |  7
 3 | 4 | 3 | 12

...it is possible, certainly, but I guess you want to rather have
different column names.  Then aliasing table names doesn't
change column names, "tab2.b" will be "b" just as well as "alias.b"
will be column labeled "b".

If you want to make a quick&ugly trick, do something like:

qnex=# CREATE VIEW tab2_renamed AS SELECT a AS tab2_a, b AS tab2_b FROM tab2;
qnex=# SELECT * FROM tab1 JOIN tab2_renamed ON (a=tab2_a);
 a | b | tab2_a | tab2_b
---+---+--------+--------
 1 | 2 |      1 |      7
 3 | 4 |      3 |     12

Other than that, I don't see too many options.  Also, consider using * in
queries as a bad coding style, and try to avoid it.

  Regards,
      Dawid

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