Thread: Feature suggestion: auto-prefixing SELECT query column names withtable/alias names

Hello,

I've seen some situations where it would be very handy if PostgreSQL 
could automatically prefix column names in a SELECT query with their 
table names (or its alias).   So for two tables, a & b, each with 
columns "id" and "title":

     SELECT * FROM a , b;

instead of returning ambiguously-named columns:

     id, title, id, title

would return:

     a.id, a.title, b.id, b.title

Of course, this can be achieved by avoiding the (often frowned-upon) 
SELECT * syntax in the first place and using explicit column names, but 
that makes otherwise short queries much longer and harder to maintain.  
Also this doesn't only apply to SELECT * queries: if tables a & b had 
more columns, but I wanted just the "title" column of each, to avoid 
ambiguous column names or having to use column position indexes, I have 
to give explicit aliases to those columns (which could be anything, but 
in practice is often the table prefix as already written in the select 
(SELECT a.title AS "a.title". b.title as "b.title" etc).

Could there be an (admittedly non-standard) option to automatically 
prefix table/alias names to columns? From searching, it seems a number 
of people would find this useful[1].  Perhaps something like:   SELECT 
#* FROM a, b    -->   a.id, a.title, b.id, d.title.  This would allow 
use per-table:   SELECT a.id, #b.*   -->   id, b.id, b.title.   Or per 
column  E.g:   SELECT a.id, #a.title, #b.title   -->   id, a.title. 
b.title.   Or alternatively it could be specified in the FROM clause as 
to which tables have their columns prefixed.

I have no idea if this is viable/sensible/anathema, especially given the 
understandable reluctance to stray from the SQL standard, but thought 
I'd ask, especially as it seems like something others would find useful too.

Thanks,

Guy

[1]

https://stackoverflow.com/questions/329931/sql-select-join-is-it-possible-to-prefix-all-columns-as-prefix

https://stackoverflow.com/questions/13153344/in-a-join-how-to-prefix-all-column-names-with-the-table-it-came-from

https://stackoverflow.com/questions/53947657/how-to-display-table-name-as-prefix-to-column-name-in-sql-result

https://stackoverflow.com/questions/33364392/sql-join-prefix-fields-with-table-name

On Mon, 2020-06-22 at 14:16 +1200, Guy Burgess wrote:
> I've seen some situations where it would be very handy if PostgreSQL 
> could automatically prefix column names in a SELECT query with their 
> table names (or its alias).   So for two tables, a & b, each with 
> columns "id" and "title":
> 
>      SELECT * FROM a , b;
> 
> instead of returning ambiguously-named columns:
> 
>      id, title, id, title
> 
> would return:
> 
>      a.id, a.title, b.id, b.title
> 
> Of course, this can be achieved by avoiding the (often frowned-upon) 
> SELECT * syntax in the first place and using explicit column names, but 
> that makes otherwise short queries much longer and harder to maintain.  
> Also this doesn't only apply to SELECT * queries: if tables a & b had 
> more columns, but I wanted just the "title" column of each, to avoid 
> ambiguous column names or having to use column position indexes, I have 
> to give explicit aliases to those columns (which could be anything, but 
> in practice is often the table prefix as already written in the select 
> (SELECT a.title AS "a.title". b.title as "b.title" etc).
> 
> Could there be an (admittedly non-standard) option to automatically 
> prefix table/alias names to columns? From searching, it seems a number 
> of people would find this useful[1].  Perhaps something like:   SELECT 
> #* FROM a, b    -->   a.id, a.title, b.id, d.title.  This would allow 
> use per-table:   SELECT a.id, #b.*   -->   id, b.id, b.title.   Or per 
> column  E.g:   SELECT a.id, #a.title, #b.title   -->   id, a.title. 
> b.title.   Or alternatively it could be specified in the FROM clause as 
> to which tables have their columns prefixed.
> 
> I have no idea if this is viable/sensible/anathema, especially given the 
> understandable reluctance to stray from the SQL standard, but thought 
> I'd ask, especially as it seems like something others would find useful too.

I'd say that this idea is not so desirable, for one because the resulting
names would not follow the name rules for SQL identifiers, so that you'd
have to double quote them whenever you reference them.
That could of course be avoided by using "a_id", "a_title" and so on.

Then there is the case of "a JOIN b USING (somecol)".
Here, "somecol" will appear in the output only once.  How should it be
labeled?  If you say "not at all", then what do you want to happen for

  SELECT * FROM a JOIN b USING (id) JOIN c USING (x)

where all three tables have a column "id"?  You see, it is not so simple.

But the real objection I have is that the problem can be easily avoided
by spelling out the SELECT list and using aliases.  Either you are talking
about ad-hoc queries, or you want to use "SELECT *" in your code.

- For ad-hoc queries I don't see the problem so much.  Here, the tables
  will be returned in the order you specify them in the query.
  So if you have "b JOIN a", the result columns will always be
  first all columns from "b", then all columns from "a".
  So you can easily figure out which column belongs to which table.

- For queries in your code, using "SELECT *" is a very bad idea.
  There is a real risk of your code breaking if the database changes.
  In code, you are probably not ready to deal with a changing number
  of columns.

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com

Then there is the case of "a JOIN b USING (somecol)".
Here, "somecol" will appear in the output only once.  How should it be
labeled?  If you say "not at all", then what do you want to happen for
 SELECT * FROM a JOIN b USING (id) JOIN c USING (x)

where all three tables have a column "id"?

Thanks, I've learned a tip about USING :)  In such case, could it not simply return all columns prefixed with their table/alias names, as that is what the table-prefix option would mean? To pretend-modify the documentation: "Also, USING implies that only one of each pair of equivalent columns will be included in the join output, not both; unless table-prefixing is enabled for one or both of columns."  In other words, if table-prefixing is enabled for a column in a USING clause, it behaves like a regular LEFT JOIN.  So SELECT #* FROM a JOIN b USING (id) would give:

    a.id, a.title, b.id, b.title

But the real objection I have is that the problem can be easily avoided
by spelling out the SELECT list and using aliases.  Either you are talking
about ad-hoc queries, or you want to use "SELECT *" in your code.

- For ad-hoc queries I don't see the problem so much.  Here, the tables will be returned in the order you specify them in the query. So if you have "b JOIN a", the result columns will always be first all columns from "b", then all columns from "a". So you can easily figure out which column belongs to which table.

True, but it would be nice to have an easy 'foolproof' way to see the table name a column belongs to (especially for wide tables).

- For queries in your code, using "SELECT *" is a very bad idea. There is a real risk of your code breaking if the database changes. In code, you are probably not ready to deal with a changing number of columns.

Yes. Though in the case of explicit SELECT lists, duplicate column names usually requires an alias to be given (unless using column index), and this is often just the table prefix anyway, so it would be nice to be able to automatically prefix the table name in the query.  It would be rather like the USING clause itself - just a nice convenience/shorthand to have.

Thanks for the comments!