Database select all data with table qualifier in PostgreSQL

Question

I need to select from a database some tables with many columns. So I want to select

select t1.*,t2.*,t3.*
from table1 t1 
left outer join table2 t2 on ...
left outer join tabl 3 t3 on ...
...

Some keys are identically named, but - due to the outer command - sometimes null. Unfortunately I cannot directly see whether the key comes from t1, t2 or t3. Is there any chance to add automatically the tablenames or another separating/distinguishing qualifier to all column names, i.e. t1_thekey, t2_thekey, t3_thekey ...?

Not automatically. You need to rename each column one at a time. — Gordon Linoff
– Gordon Linoff, Commented Sep 21, 2020 at 12:45
To add to the above, many consider SELECT * to be undesirable, because its meaning changes as the number of columns in the table may change. By explicitly listing out each column, you avoid this problem. — Tim Biegeleisen
– Tim Biegeleisen, Commented Sep 21, 2020 at 12:47
It becomes more tricky as I use it inside node-red. Since I get a JSON back, each key (= column name) is allowed only once. This results in overwriting existing values with null by the missing data set from one of the left outer tables. As there is no solution I definitely have to rename the columns manually. — Michael Hecht
– Michael Hecht, Commented Sep 21, 2020 at 13:08

Gordon Linoff · Accepted Answer · 2020-09-21 12:46:32Z

2

Postgres doesn't allow you to rename the columns en masse. However, you could select the columns as records, so you can see where they come from:

select t1, t2, t3

answered Sep 21, 2020 at 12:46

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Michael Hecht Over a year ago

Ok, interesting workaround. Thank you. ... But the column names are than missing?!

Gordon Linoff Over a year ago

@MichaelHecht . . . You have a record name and then you have a field name within the records.

Collectives™ on Stack Overflow

Database select all data with table qualifier in PostgreSQL

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related