Re: Default order on displaying data without order clause

If someone wants to log a feature request for this, it'll probably get done
relatively soon. It's not an unreasonable request for the View/Edit data
tool (obviously not the Query Tool mode, as discussed).

On Fri, Mar 15, 2019 at 6:36 PM Dave Caughey <caugheyd(at)gmail(dot)com> wrote:

> 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(at)artsman(dot)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(at)artsman(dot)com <doug(at)artsman(dot)com>
>> http://www.artsman.com
>> Phone (403) 650-1978
>>
>> On Mar 15, 2019, at 8:24 AM, Dave Caughey <caugheyd(at)gmail(dot)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(dot)bertels(at)ucll(dot)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"
>>>
>>>
>>

--
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company