On Sat, Oct 24, 2015 at 3:42 PM, Rafal Pietrak <rafal@xxxxxxxxx> wrote:
Good luck,
Dane
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 <rafal@xxxxxxxxx
>> <mailto:rafal@xxxxxxxxx>>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@xxxxxxxxxxxxxx)
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)
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
