Re: ORDER BY TABLENAME, possible bug

On Fri, Nov 18, 2016 at 3:35 PM, Pantelis Theodosiou <ypercube(at)gmail(dot)com>
wrote:

>
>
> On Sat, Oct 29, 2016 at 3:23 PM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
> wrote:
>
>> Hi
>>
>> 2016-10-29 14:13 GMT+02:00 dv <udv(dot)mail(at)gmail(dot)com>:
>>
>>> E.g. query:
>>>
>>> SELECT col1, col2, col3
>>> FROM table1
>>> ORDER BY table1
>>>
>>> Postgres uses col1 for ASC ordering, if we write "ORDER BY table1
>>> DESC" then DESC-ordering. I'm not sure this is a bug, but didn't find
>>> description for such behaviour.
>>>
>>
>> It is not bug. Postgresql's table has fictive column with same name as
>> tablename that is composite of all columns
>>
>>
>>
> Is this somewhere in the documentation? The only place I could find where
> there is a hint of this use, is the Note in Row Constructors in
> https://www.postgresql.org/docs/current/static/sql-expressions.html that
> uses a table alias without the .* in an expression: ROW(t, 42)
>

​https://www.postgresql.org/docs/9.6/static/rowtypes.html

​"Whenever you create a table, a composite type is also automatically
created, with the same name as the table, to represent the table's row
type."

So, its documented and in technically correct location. I'm not sure if
introducing this material in a "tutorial" would be a gain or just confuse
the student. It seems to be something one picks up somehow (trial and
error, mailing list, stumbling upon it in the docs or elsewhere on the
Internet) as one increases their knowledge of SQL to an intermediate level.

David J.