Обсуждение: Default order of select datname from pg_database

Hi David,

Ordering can never be assumed in SQL. If you want a particular order then you need an order by.

Hope that hopes!

Jason

> On Apr 19, 2014, at 1:19 AM, Elías David <elias.moreno.tec@gmail.com> wrote:
>
> Hi all,
>
> By any change, does the following query have an "order by" by default when omitted?:
>
> select datname from pg_database;
>
> I know you could pass and ORDER BY datname or similar but what I don't know is if by default there's some kind of
sortwhen the order by is omitted. 
>
> I executed that query against one of my servers and while looking at the names it seems complety random, the order is
alwaysthe same, for instance: 
>
> web2
> template0
> asdf
> basic
> postgres
> web3
> .....
>
> Does anybody know?
>
> Thanks in advance!
>
> --
> Elías David.

--


This transmission contains confidential and privileged information intended
solely for the party identified above. If you receive this message in
error, you must not use it or convey it to others. Please destroy it
immediately and contact the sender at (303) 386-3955 or by return e-mail to
the sender.

=?UTF-8?B?RWzDrWFzIERhdmlk?= <elias.moreno.tec@gmail.com> writes:
> By any change, does the following query have an "order by" by default when
> omitted?:

> select datname from pg_database;

No.  No SQL query ever has a "default" order by.

> I executed that query against one of my servers and while looking at the
> names it seems complety random, the order is always the same, for instance:

An unqualified query is typically going to result in a sequential scan
of the table, so what you're seeing is the current physical order of the
tuples.  This might change after adding/deleting/changing the properties
of a database.

            regards, tom lane

Tom Lane-2 wrote
> =?UTF-8?B?RWzDrWFzIERhdmlk?= <

> elias.moreno.tec@

> > writes:
>> By any change, does the following query have an "order by" by default
>> when
>> omitted?:
>
>> select datname from pg_database;
>
> No.  No SQL query ever has a "default" order by.

While not applicable here you can define a view to include an order by so
that a plain select on the view is indeed ordered even though the explicit
code provided by the user does not specify an order.

I'm doubting, though, that any of the information schema views do this.

David J.



--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Default-order-of-select-datname-from-pg-database-tp5800783p5800802.html
Sent from the PostgreSQL - admin mailing list archive at Nabble.com.

Ah... I see, that clarifies things. Thank you all for your responses!

All started because I looked a script that is used in our company to backup databases and it seemed odd that the query didn't use an order by sentence which would be helpful, that way I could anticipate when a particular database is going to be backed up, it's easy to fix though so no issues there.

Again thank you all

Best regards