On Sat, Oct 24, 2015 at 5:23 PM, Dane Foster <studdu...@gmail.com> wrote:
> On Sat, Oct 24, 2015 at 3:42 PM, Rafal Pietrak <ra...@ztk-rp.eu> wrote: > >> >> >> W dniu 24.10.2015 o 21:03, Rafal Pietrak pisze: >> > >> > >> > W dniu 24.10.2015 o 15:00, David G. Johnston pisze: >> >> On Sat, Oct 24, 2015 at 6:41 AM, Rafal Pietrak <ra...@ztk-rp.eu >> >> <mailto:ra...@ztk-rp.eu>>wrote: >> > [----------------------] >> >> >> >> Using explicit column names is expected - using "*" in non-trivial and >> >> production queries is not. >> >> >> >> You can move the aliases if you would like. >> >> >> >> SELECT * >> >> FROM tablea (col1, col2, col4) >> >> JOIN tableb AS tb1 (col1, col3, col5) USING (col1) >> >> JOIN tableb AS tb2 >> >> >> >> (col1, col6, col7) USING (col1) >> > >> > I knew there must have been something like this. >> >> Upss. Almost, but not quite. I've just read the manual on that >> (http://www.postgresql.org/docs/9.4/static/queries-table-expressions.html >> ) >> and it looks like "col1", "col2", etc in the above example are column >> *aliases*. Right? >> >> So I have to list *all* the columns of the aliased table irrespectively >> if I need any of them within the output, or not. >> >> It's a pity standard didn't choose to make column aliasing optional, >> allowing for cherry pick what's aliased like following: >> >> .. JOIN table AS tb(column7 [as alias1], column3 [as alias2],...) >> >> thenx anyway, "Mandatory" column aliasing is helpfull too. >> >> -R >> >> >> >> >> -- >> Sent via pgsql-general mailing list (pgsql-general@postgresql.org) >> To make changes to your subscription: >> http://www.postgresql.org/mailpref/pgsql-general >> > > You may be able to accomplish that using aliased sub-selects as in-line > views. The purpose of the sub-selects in this use-case is simply to cherry > pick the columns you want. > SELECT * > FROM > (SELECT col1, col2, col4 FROM tablea) AS iv > JOIN (SELECT co1, col3, col5 FROM tableb) AS tb1 USING (col1)) > JOIN (SELECT col1, col6, col7 FROM tableb) AS tb22 USING (col1) > > Please note, this may be a performance nightmare for large tables because > w/o a WHERE clause that can be pushed down to the sub-selects each > sub-select will do a full table scan. > > Please note that the 3rd JOIN clause is nutty (I translated it from your > original) because why would you join a table to itself just to select a > different set of columns? > > Good luck, > > Dane > For the record SELECT * in my example is absolutely the wrong thing to do but your original didn't leave me w/ any other option. Dane
