Re: Default order on displaying data without order clause

On Mar 15, 2019, at 8:24 AM, Dave Caughey <caugheyd@gmail.com> wrote:

Yeah, but that's just what the spec says about how the SELECT works.  It's not a statement about how a user interface should display information.

Presenting unordered information is basically guaranteed to be never what the user wants.  In  this case, the user has to almost always edit the query to add a sensible "order by" clause.

On the other hand, picking a default column (e.g., PK, or insertion order) is probably going to be the desirable behaviour most of the time (but rarely, not).  In this case, the user has almost never edit the query to remove/alter the default "order by" clause.

So you've got a UI decision of doing something that'll be "right" (i.e., what the user wants) most of the time, or "wrong" (i.e., what the user doesn't want) most of the time.   This is an easy win.

Cheers,

Dave

On Fri, Mar 15, 2019 at 9:19 AM Wim Bertels <wim.bertels@ucll.be> wrote:

Janus schreef op vr 15-03-2019 om 12:20 [+0100]:
> In pgAdmin 3, an implicit default order by primary key is used when
> displaying data without an otherwise stated order. This is not the
> case in pgAdmin 4. The order seems somewhat arbitrary and the
> following statement from this site is definitely not true: "When you
> query data from a table, PostgreSQL returns the rows in the order
> that they were inserted into the table."

Hallo Janus,

this standard behaviour, as described by the ISO standard
:: ie unless you specify an ORDER BY clause, the order can be anything,
 like a (unordered) set;

the example of "the way there were inserted", this might often be the
case, but it's not a rule
more background: see planner, shared mem and cache

>
> I do understand that some order of course exists, but from a
> human perspective it would make sense that default ordering is by PK,
> unless stated otherwise. In PgAdmin 4, is it possible to set a
> default ordering, at least so that displaying a table's contents
> using the "View Data" button, i.e. the query being run is altered
> from "SELECT oid, * FROM public.mytable" to "SELECT oid, * FROM
> public.mytable ORDER BY <primary key(s)>"?

i only see the filter/sort option

>
> Thanks!
--
mvg,
Wim Bertels
--
Lector
UC Leuven-Limburg
--
Let me take you a button-hole lower.
                -- William Shakespeare, "Love's Labour's Lost"