public inbox for [email protected]help / color / mirror / Atom feed
Docs claim that "select myTable.*" wildcard won't let you assign column names 6+ messages / 3 participants [nested] [flat]
* Docs claim that "select myTable.*" wildcard won't let you assign column names @ 2015-09-21 22:37 Amir Rohan <[email protected]> 0 siblings, 1 reply; 6+ messages in thread From: Amir Rohan @ 2015-09-21 22:37 UTC (permalink / raw) To: pgsql-docs <html><head></head><body><div style="font-family: Verdana;font-size: 12.0px;"><div> </div> <div>From http://www.postgresql.org/docs/9.4/static/sql-select.html (and previous version too):</div> <div> <h3><tt class="COMMAND"> ##SELECT</tt> List</h3> <div> <...></div> </div> <div> Instead of an expression, <tt class="LITERAL">*</tt> can be written in the output list as a shorthand for all the columns of the selected rows.</div> <div> Also, you can write <tt class="LITERAL"><tt class="REPLACEABLE c2">table_name</tt>.*</tt> as a shorthand for the columns coming from just that table. In these cases it is not</div> <div> possible to specify new names with <tt class="LITERAL">AS</tt>; the output column names will be the same as the table columns' names.</div> <div> </div> <div>But, the docs elsewhere feature a query example show the use of a wildcard for columns</div> <div>as well as allowing you to assign names to as many of the leading columns as you wish:</div> <div> <div> <div>WITH T0 as ( SELECT 1,2,3 )<br/> SELECT T0.* from T0 as T0(foo,bar) ;</div> <div> </div> <div> <div> foo │ bar │ ?column?<br/> ─────┼─────┼──────────<br/> 1 │ 2 │ 3<br/> (1 row)</div> <div> </div> <div>The following curious variant also works:</div> <div> </div> <div>WITH T0 as ( SELECT 1,2,3 )<br/> SELECT justAnythingReally.* from T0 as justAnythingReally(foo,bar) ;</div> </div> <div>The synoposis/grammer at the top doesn't hint at this either. I've checked and this has been supported since at least 9.2 .</div> <div> </div> <div>Regards,</div> <div>Amir</div> <div> </div> </div> </div></div></body></html> ^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Docs claim that "select myTable.*" wildcard won't let you assign column names @ 2015-09-21 23:39 David G. Johnston <[email protected]> parent: Amir Rohan <[email protected]> 0 siblings, 1 reply; 6+ messages in thread From: David G. Johnston @ 2015-09-21 23:39 UTC (permalink / raw) To: Amir Rohan <[email protected]>; +Cc: pgsql-docs On Monday, September 21, 2015, Amir Rohan <[email protected]> wrote: > > From http://www.postgresql.org/docs/9.4/static/sql-select.html (and > previous version too): > ##SELECT List > <...> > Instead of an expression, * can be written in the output list as a > shorthand for all the columns of the selected rows. > Also, you can write table_name.* as a shorthand for the columns > coming from just that table. In these cases it is not > possible to specify new names with AS; the output column names will > be the same as the table columns' names. > > But, the docs elsewhere feature a query example show the use of a wildcard > for columns > as well as allowing you to assign names to as many of the leading columns > as you wish: > > WITH T0 as ( SELECT 1,2,3 ) > SELECT T0.* from T0 as T0(foo,bar) ; > > foo │ bar │ ?column? > ─────┼─────┼────────── > 1 │ 2 │ 3 > (1 row) > > The following curious variant also works: > > WITH T0 as ( SELECT 1,2,3 ) > SELECT justAnythingReally.* from T0 as justAnythingReally(foo,bar) ; > > The synoposis/grammer at the top doesn't hint at this either. I've checked > and this has been supported since at least 9.2 . > > Neither of those examples is: SELECT * AS "how would one alias this?" FROM table So what's your point? Obviously you can alias stuff before it makes its way into a select-list that refers to it using * In this case the FROM clause is what is being alised. It is documented though I'd need to look to identify the specific location. It would not be documented in a section regarding the select-list. David J. ^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Docs claim that "select myTable.*" wildcard won't let you assign column names @ 2015-09-22 01:04 Amir Rohan <[email protected]> parent: David G. Johnston <[email protected]> 0 siblings, 2 replies; 6+ messages in thread From: Amir Rohan @ 2015-09-22 01:04 UTC (permalink / raw) To: David G. Johnston <[email protected]>; +Cc: pgsql-docs On Monday, September 21, 2015, Amir Rohan <[email protected]> wrote: >> From http://www.postgresql.org/docs/9.4/static/sql-select.html[http://www.postgresql.org/docs/9.4/static/sql->; select.html] (and previous version too): >> >> ##SELECT List >> >> <...> >> Instead of an expression, * can be written in the output list as a shorthand for all the columns of the >> selected rows. >> Also, you can write table_name.* as a shorthand for the columns coming from just that table. In these >> cases it is not possible to specify new names with AS; the output column names will be the same as the >> table columns' names. >> >> But, the docs elsewhere feature a query example show the use of a wildcard for columns >> as well as allowing you to assign names to as many of the leading columns as you wish: >> >> >> WITH T0 as ( SELECT 1,2,3 ) >> SELECT T0.* from T0 as T0(foo,bar) ;<...> On Monday, September 21, 2015, David G. Johnston wrote: > Neither of those examples is: > > SELECT * AS "how would one alias this?" FROM table > > So what's your point? My point is that "In these cases it is not possible to specify new names with AS" is misleading because it *is* possible and useful, but requires syntax which isn't clearly shown (if at all) where I'd expect it. I think that could be improved. > Obviously you can alias stuff before it makes its way into a select-list that refers to it using * "obvious" to whom? probably not to someone who's level of SQL mastery has brought him/her to reading the exciting "SELECT" documentation. I do see your point though, in the grammar the "AS" in my example belongs not to `output_name ` but to `from_item`. So this syntax is hidden away behind the `column_alias` production. > In this case the FROM clause is what is being aliased. It is documented though I'd need to look to > identify the specific location. This belongs in the page describing SELECT, and though I've looked I haven't found it. If I'm wrong ( I did look again just now), please correct me. Amir -- Sent via pgsql-docs mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-docs ^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Docs claim that "select myTable.*" wildcard won't let you assign column names @ 2015-09-22 01:32 Joe Conway <[email protected]> parent: Amir Rohan <[email protected]> 1 sibling, 0 replies; 6+ messages in thread From: Joe Conway @ 2015-09-22 01:32 UTC (permalink / raw) To: Amir Rohan <[email protected]>; David G. Johnston <[email protected]>; +Cc: pgsql-docs On 09/21/2015 06:04 PM, Amir Rohan wrote: > This belongs in the page describing SELECT, and though I've looked I haven't found it. If I'm wrong ( > I did look again just now), please correct me. Feel free to submit a doc patch if you feel it needs to be improved, but it is mentioned here: http://www.postgresql.org/docs/9.4/interactive/sql-select.html#SQL-FROM 8<----------- alias A substitute name for the FROM item [...] If an alias is written, a column alias list can also be written to provide substitute names for one or more columns of the table. 8<----------- Joe -- Crunchy Data - http://crunchydata.com PostgreSQL Support for Secure Enterprises Consulting, Training, & Open Source Development Attachments: [application/pgp-signature] signature.asc (836B, 2-signature.asc) download ^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Docs claim that "select myTable.*" wildcard won't let you assign column names @ 2015-09-22 01:58 David G. Johnston <[email protected]> parent: Amir Rohan <[email protected]> 1 sibling, 1 reply; 6+ messages in thread From: David G. Johnston @ 2015-09-22 01:58 UTC (permalink / raw) To: Amir Rohan <[email protected]>; +Cc: pgsql-docs On Monday, September 21, 2015, Amir Rohan <[email protected]> wrote: > On Monday, September 21, 2015, Amir Rohan <[email protected] > <javascript:;>> wrote: > > > >> From > http://www.postgresql.org/docs/9.4/static/sql-select.html[http://www.postgresql.org/docs/9.4/static/sql->; > select.html] (and previous version too): > >> > >> ##SELECT List > >> > >> <...> > >> Instead of an expression, * can be written in the output list as a > shorthand for all the columns of the > >> selected rows. > >> Also, you can write table_name.* as a shorthand for the columns > coming from just that table. In these > >> cases it is not possible to specify new names with AS; the output > column names will be the same as the > >> table columns' names. > >> > >> But, the docs elsewhere feature a query example show the use of a > wildcard for columns > >> as well as allowing you to assign names to as many of the leading > columns as you wish: > >> > >> > >> WITH T0 as ( SELECT 1,2,3 ) > >> SELECT T0.* from T0 as T0(foo,bar) ;<...> > > On Monday, September 21, 2015, David G. Johnston wrote: > > > Neither of those examples is: > > > > SELECT * AS "how would one alias this?" FROM table > > > > So what's your point? > > My point is that "In these cases it is not possible to specify new names > with AS" is misleading because > it *is* possible and useful, but requires syntax which isn't clearly shown > (if at all) where I'd expect it. > I think that could be improved. > > > Obviously you can alias stuff before it makes its way into a select-list > that refers to it using * > > "obvious" to whom? probably not to someone who's level of SQL mastery has > brought him/her to reading > the exciting "SELECT" documentation. I do see your point though, in the > grammar the "AS" in my example > belongs not to `output_name ` but to `from_item`. So this syntax is hidden > away behind the `column_alias` > production. > > > In this case the FROM clause is what is being aliased. It is documented > though I'd need to look to > > identify the specific location. > > This belongs in the page describing SELECT, and though I've looked I > haven't found it. If I'm wrong ( > I did look again just now), please correct me. > > To be honest, if you have a situation where you think you need to alias "*" you should probably be expanding that "*" out into individual column names anyway, which you can easily alias. The docs don't make a big deal of alising other than noting individually where they can happen (cte, from clause, select-list) and few questions that are raised are probably better served to be simply handled on these lists than cluttering the docs. David J. ^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Docs claim that "select myTable.*" wildcard won't let you assign column names @ 2015-09-22 19:40 Amir Rohan <[email protected]> parent: David G. Johnston <[email protected]> 0 siblings, 0 replies; 6+ messages in thread From: Amir Rohan @ 2015-09-22 19:40 UTC (permalink / raw) To: David G. Johnston <[email protected]>; +Cc: pgsql-docs On Monday, September 21, 2015, David G. Johnston wrote: >To be honest, if you have a situation where you think you need to alias "*" >you should probably be expanding that "*" out into individual column names >anyway, which you can easily alias. > That depends. If my table has 30 columns and I just need to rename the first couple, then I probably would find that advice unreasonable. >The docs don't make a big deal of alising other than noting individually >where they can happen (cte, from clause, select-list) and few questions >that are raised are probably better served to be simply handled on these >lists than cluttering the docs. What constitutes "clutter", or what is "obvious", really depends on who is making the assertion. A pg veteran's views probably don't mirrort those of the person you'd typically find actually reading the docs, trying to figure out how to write the query they want. But yeah, the pg documentation isn't the best resource to pick up SQL from (nor is is their purpose), and no patch I'd submit would really change that. So, we're done. Amir -- Sent via pgsql-docs mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-docs ^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2015-09-22 19:40 UTC | newest] Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2015-09-21 22:37 Docs claim that "select myTable.*" wildcard won't let you assign column names Amir Rohan <[email protected]> 2015-09-21 23:39 ` David G. Johnston <[email protected]> 2015-09-22 01:04 ` Amir Rohan <[email protected]> 2015-09-22 01:32 ` Joe Conway <[email protected]> 2015-09-22 01:58 ` David G. Johnston <[email protected]> 2015-09-22 19:40 ` Amir Rohan <[email protected]>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox