In mysql 5.1, you can this:

select a.*, b.title as btitle from tablename a inner join tablename b
on a.bid = b.id;

but what I want to do is make a prefix name for every column that comes
back from the b table, like:

select a.*, b.* as btable.* from tablename a inner join tablename b on
a.bid = b.id;

So that, in the results, everything that comes back from the b table is
in columns names like like:

btable.title | btable.price | btable.description

Can I do that? I'm kind of suprised if I can't, actally.. Could I do
that in postgre?

dterrors (AT) hotmail (DOT) com wrote:
No, you can't. It isn't part of the SQL standard. You need to give
column aliases one at a time.

By the way, MySQL doesn't return the table name (nor the correlation
name) in the column label of the result set anyway. You'd get column
labels of "title", "price", "description", not "btable.title",
"btable.price", "btable.description".

You need to alias them individually, and put the label in delimiters
because it contains a dot character:

SELECT ...
btable.title AS `btable.title`,
btable.price AS `btable.price`,
btable.description AS `btable.description`, ...

For what it's worth, I never use * or table.* in my queries. Doing that
hides errors, if I ever change the table structure. For instance, if I
change the name of a column, I'd like the SQL query to give me an error,
as specific as possible.

If I were to use * wildcards, no error would be given, but the column
wouldn't exist in the result set by the name I expect. I typically
fetch values by column name from a row of the result set, so I'm likely
to simply get blanks back. Then it's more troublesome to figure out why
I have blanks in my web page where I should have data.

http://www.postgresql.org/docs/8.1/i...ql-select.html says:

SELECT [ ALL | DISTINCT [ ON ( expression [, ...] ) ] ]
* | expression [ AS output_name ] [, ...]

This doesn't appear to support column aliasing for wildcards like you
describe. In fact, based on that syntax description, it doesn't appear
to support b.* at all.

Regards,
Bill K.