Re: Feature suggestion: auto-prefixing SELECT query column names with table/alias names

On 22/06/2020 3:25 pm, Laurenz Albe wrote:
> 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!