Re: Default order on displaying data without order clause

I think you're confusing my point.

You're right that you never want to add an *implicit* default to the order clause.  That would be changing the SQL standard which, as you've noted, would be really bad.

But, we're talking about a tool that constructs *explicit* queries in order to display the data in the table...  I.e., when you right-click on a table in pgAdmin and select "View/Edit Data...", pgAdmin is fully in control of whether it generates either

SELECT * FROM public.sometable

or

SELECT * FROM public.sometable ORDER BY somecolumn ASC

In the second case, there is no *implicit* default... it's very explicitly specified, and does not in anyway change/contradict the SQL standard.  It's just the tool stepping in and doing what I end up having to do manually. every. frickin'. time.

Another UI concept that could be applied here is to simply remember my previous settings, on a table-by-table basis.   E.g., if I do a "View/Edit Data..." once, and *explicitly* change the ORDER BY clause to be "ORDER BY price DESC" then it's very a very smart thing to add to SELECT the next time I do a "View/Edit Data..." on that table.  And if I subsequently change to be "ORDER BY qty ASC" then that becomes a smart default to continue to use on subsequent "View/Edit Data..." queries.  Or, if I actually remove the default ORDER BY, then it's smart thing to remember that choice and continue to use no ORDER BY.

But certainly, if the user explicitly indicates that they *do* want an ORDER BY clause, then continuously defaulting to no ORDER BY clause each time they do a "View/Edit Data...", on the same table, is arbitrarily picking a behaviour that you clearly know that the user doesn't prefer.  Why not just do for the user the same thing they explicitly asked you to do for last time?  From a UX perspective, this approach requires the least amount of interaction by the user in order to get what exactly they want.

And from a UX design perspective, the advantage of the this approach is that it doesn't require yet-another option to support/document.  

Cheers,

Dave

On Fri, Mar 15, 2019 at 1:59 PM Doug Easterbrook <doug@artsman.com> wrote:

I’m  disagreeing with dave.

to be clear.    you NEVER want to add an implicit default to the order clause.  EVER.

Postgres returns data in the order that it returns it (sql standard) unless you specify otherwise.

the python mantra:  it is better to be EXPLICIT than IMPLICIT.  

If you do not provide an explicit sort order and rely on some implicit ordering, then your code will break in the future if you are relying on the order from the engine… and its harder to find problems if pgadmin has different behaviour than sending the same query through  psql or the postgres libraries.

final point is that PGadmin just gives the database your query.    if Pgadmin altered my SQL code for me, I’d be angry since i’ve wanted to know what order postgres felt it wanted to return the data.

its not pgadmin's job to fix my mistakes.   it my job to fix my mistakes.

do I agree that people want a natural order in a gui.     Absolutely  but you need to be explicit which one.

Doug Easterbrook

Arts Management Systems Ltd.

mailto:doug@artsman.com

http://www.artsman.com

Phone (403) 650-1978

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"