Re: Feature suggestion: auto-prefixing SELECT query column nameswith table/alias names - Mailing list pgsql-general

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