Thread: wildcard alias

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.

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   <kleptog@svana.org>   http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.

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

On 11/10/06, Matthew Terenzio <matt@jobsforge.com> 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