Re: [RFE] auto ORDER BY for SELECT

Piling On.

NO! (not that I actually think this would ever happen anyway).

Sorting is a resource-intensive process and it should NOT be made into a
default. Besides, any kind of intelligent/implicit behavior like that ends
up being forgotten and/or changed in the future and previously working
queries begin to break.

SQL is an explicit, declarative, language and thus will tend toward having
verbose syntax generally - this is a good thing since while you are in the
middle of writing a query you may be frustrated above having to explain
every little thing but when you come back to the code a year from now you
have a good chance of knowing exactly what it is doing AND when you upgrade
PostgreSQL you have less exposure to the breakage of existing queries since
the server is not making guesses as to what you want/mean.

David J.

From: pgsql-general-owner(at)postgresql(dot)org
[mailto:pgsql-general-owner(at)postgresql(dot)org] On Behalf Of Douglas Eric
Sent: Monday, January 23, 2012 7:18 AM
To: pgsql-general(at)postgresql(dot)org
Subject: [GENERAL] [RFE] auto ORDER BY for SELECT

I'm not sure if this is the right list to discuss this, but, I have a
suggestion:

ORDER BY clause, as defined in the SELECT documentation
<http://www.postgresql.org/docs/9.1/static/sql-select.html> says:

"If ORDER BY is not given, the rows are returned in whatever order the
system finds fastest to produce"

This order is usually not wanted, as it is not predictable. I believe many
people would expect the order of rows

returned in this case, to be ordered as the primary key of the table, or the
same order the rows were inserted.

I suggest to change this behavior. If one makes a SELECT statement without
any ORDER BY, it would be

clever to automatically sort by the first primary key found in the query, if
any.

The present behavior would still be used in case of queries without any
primary key fields.

This would save a lot of repeated clauses "ORDER BY <table primary key>"

that we have to add to every SELECT, even the most simple one

"SELECT * FROM <table>"

If we actually want the order of the rows to make any sense.