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 <d...@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:d...@artsman.com <d...@artsman.com> > http://www.artsman.com > Phone (403) 650-1978 > > On Mar 15, 2019, at 8:24 AM, Dave Caughey <caugh...@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.bert...@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" >> >> >
