pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedDefault order of select datname from pg_database
5+ messages / 4 participants
[nested] [flat]
* Default order of select datname from pg_database
@ 2014-04-19 07:19 Elías David <elias.moreno.tec@gmail.com>
2014-04-19 14:45 ` Re: Default order of select datname from pg_database Jason Mathis <jmathis@redzonesoftware.com>
2014-04-19 15:17 ` Re: Default order of select datname from pg_database Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 5+ messages in thread
From: Elías David @ 2014-04-19 07:19 UTC (permalink / raw)
To: pgsql-admin
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 sort when 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 always the same, for instance:
web2
template0
asdf
basic
postgres
web3
.....
Does anybody know?
Thanks in advance!
--
Elías David.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Default order of select datname from pg_database
2014-04-19 07:19 Default order of select datname from pg_database Elías David <elias.moreno.tec@gmail.com>
@ 2014-04-19 14:45 ` Jason Mathis <jmathis@redzonesoftware.com>
1 sibling, 0 replies; 5+ messages in thread
From: Jason Mathis @ 2014-04-19 14:45 UTC (permalink / raw)
To: Elías David <elias.moreno.tec@gmail.com>; +Cc: pgsql-admin
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 sort when 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 always the 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.
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Default order of select datname from pg_database
2014-04-19 07:19 Default order of select datname from pg_database Elías David <elias.moreno.tec@gmail.com>
@ 2014-04-19 15:17 ` Tom Lane <tgl@sss.pgh.pa.us>
2014-04-19 15:44 ` Re: Default order of select datname from pg_database David G Johnston <david.g.johnston@gmail.com>
2014-04-19 16:39 ` Re: Default order of select datname from pg_database Elías David <elias.moreno.tec@gmail.com>
1 sibling, 2 replies; 5+ messages in thread
From: Tom Lane @ 2014-04-19 15:17 UTC (permalink / raw)
To: Elías David <elias.moreno.tec@gmail.com>; +Cc: pgsql-admin
=?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
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Default order of select datname from pg_database
2014-04-19 07:19 Default order of select datname from pg_database Elías David <elias.moreno.tec@gmail.com>
2014-04-19 15:17 ` Re: Default order of select datname from pg_database Tom Lane <tgl@sss.pgh.pa.us>
@ 2014-04-19 15:44 ` David G Johnston <david.g.johnston@gmail.com>
1 sibling, 0 replies; 5+ messages in thread
From: David G Johnston @ 2014-04-19 15:44 UTC (permalink / raw)
To: pgsql-admin
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-tp5800783p5...
Sent from the PostgreSQL - admin mailing list archive at Nabble.com.
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Default order of select datname from pg_database
2014-04-19 07:19 Default order of select datname from pg_database Elías David <elias.moreno.tec@gmail.com>
2014-04-19 15:17 ` Re: Default order of select datname from pg_database Tom Lane <tgl@sss.pgh.pa.us>
@ 2014-04-19 16:39 ` Elías David <elias.moreno.tec@gmail.com>
1 sibling, 0 replies; 5+ messages in thread
From: Elías David @ 2014-04-19 16:39 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-admin
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
On Apr 19, 2014 10:47 AM, "Tom Lane" <tgl@sss.pgh.pa.us> wrote:
> =?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
>
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2014-04-19 16:39 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2014-04-19 07:19 Default order of select datname from pg_database Elías David <elias.moreno.tec@gmail.com>
2014-04-19 14:45 ` Jason Mathis <jmathis@redzonesoftware.com>
2014-04-19 15:17 ` Tom Lane <tgl@sss.pgh.pa.us>
2014-04-19 15:44 ` David G Johnston <david.g.johnston@gmail.com>
2014-04-19 16:39 ` Elías David <elias.moreno.tec@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox