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
