pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedORDER BY DESC and NULLS LAST by default
9+ messages / 7 participants
[nested] [flat]
* ORDER BY DESC and NULLS LAST by default
@ 2023-10-27 14:16 Loles <lolesft@gmail.com>
2023-10-27 14:17 ` Re: ORDER BY DESC and NULLS LAST by default M Sarwar <sarwarmd02@outlook.com>
2023-10-27 15:54 ` Re: ORDER BY DESC and NULLS LAST by default Laurenz Albe <laurenz.albe@cybertec.at>
2023-10-27 16:25 ` Re: ORDER BY DESC and NULLS LAST by default Holger Jakobs <holger@jakobs.com>
0 siblings, 3 replies; 9+ messages in thread
From: Loles @ 2023-10-27 14:16 UTC (permalink / raw)
To: pgsql-admin
Hi!
Can the server be configured so that the default ORDER BY option is DESC
instead of ASC?
And to make it NULLS LAST instead of NULLS FIRST?
Happy weekend and Thank you very much!
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: ORDER BY DESC and NULLS LAST by default
2023-10-27 14:16 ORDER BY DESC and NULLS LAST by default Loles <lolesft@gmail.com>
@ 2023-10-27 14:17 ` M Sarwar <sarwarmd02@outlook.com>
2023-10-27 14:42 ` Re: ORDER BY DESC and NULLS LAST by default Arjan Saly Consultancy (EU) <consultancy@arjansaly.eu>
2 siblings, 1 reply; 9+ messages in thread
From: M Sarwar @ 2023-10-27 14:17 UTC (permalink / raw)
To: Loles <lolesft@gmail.com>; pgsql-admin
I do not think that this is available in any of the databases.
Sarwar
________________________________
From: Loles <lolesft@gmail.com>
Sent: Friday, October 27, 2023 10:16 AM
To: pgsql-admin@postgresql.org <pgsql-admin@postgresql.org>
Subject: ORDER BY DESC and NULLS LAST by default
Hi!
Can the server be configured so that the default ORDER BY option is DESC instead of ASC?
And to make it NULLS LAST instead of NULLS FIRST?
Happy weekend and Thank you very much!
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: ORDER BY DESC and NULLS LAST by default
2023-10-27 14:16 ORDER BY DESC and NULLS LAST by default Loles <lolesft@gmail.com>
2023-10-27 14:17 ` Re: ORDER BY DESC and NULLS LAST by default M Sarwar <sarwarmd02@outlook.com>
@ 2023-10-27 14:42 ` Arjan Saly Consultancy (EU) <consultancy@arjansaly.eu>
0 siblings, 0 replies; 9+ messages in thread
From: Arjan Saly Consultancy (EU) @ 2023-10-27 14:42 UTC (permalink / raw)
To: Loles <lolesft@gmail.com>; +Cc: pgsql-admin; M Sarwar <sarwarmd02@outlook.com>
You should be able to use the ORDER BY <sort_espression> ASC NULLS LAST clause, see this page in the manuals: https://www.postgresql.org/docs/current/queries-order.html
However, if this doesn't work well, you might check your localisation and collation settings as the way a database sorts depends on these settings. They can be changed on OS level, in the database configuration and on session level, but you can also use the COLLATE statement in your query. I would expect PostgreSQL to override these settings when the ORDER BY …. NULL LAST (or FIRST) clause is used, but since I’ve never used this option, I can’t tell for sure. I have seen on other applications (not postgres) that if OS localisation or collation does not match the applications localisation/collation, things may get messed up. Mostly with special characters, but sometimes with sorting as well. To my experience this seems to be more of an issue on Windows machines than on Linux based machines.
See https://www.postgresql.org/docs/current/locale.html#LOCALE-BEHAVIOR for more information on localisation and collation in PostgreSQL.
Hope this helps!
Kind Regards,
Arjan Saly
Database & Business Intelligence Specialist
Arjan Saly Consultancy
e: consultancy@arjansaly.eu
l: https://nl.linkedin.com/in/arjansaly
w: https://www.arjansaly.eu/consultancy

> Op 27 okt 2023, om 16:17 heeft M Sarwar <sarwarmd02@outlook.com> het volgende geschreven:
>
> I do not think that this is available in any of the databases.
> Sarwar
>
> From: Loles <lolesft@gmail.com>
> Sent: Friday, October 27, 2023 10:16 AM
> To: pgsql-admin@postgresql.org <pgsql-admin@postgresql.org>
> Subject: ORDER BY DESC and NULLS LAST by default
>
> Hi!
>
> Can the server be configured so that the default ORDER BY option is DESC instead of ASC?
>
> And to make it NULLS LAST instead of NULLS FIRST?
>
> Happy weekend and Thank you very much!
Attachments:
[image/png] Mail Signature.png (17.6K, ../../31B4CC5A-ADE1-42C9-8A89-8B1EC78D8571@arjansaly.eu/3-Mail%20Signature.png)
download | view image
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: ORDER BY DESC and NULLS LAST by default
2023-10-27 14:16 ORDER BY DESC and NULLS LAST by default Loles <lolesft@gmail.com>
@ 2023-10-27 15:54 ` Laurenz Albe <laurenz.albe@cybertec.at>
2 siblings, 0 replies; 9+ messages in thread
From: Laurenz Albe @ 2023-10-27 15:54 UTC (permalink / raw)
To: Loles <lolesft@gmail.com>; pgsql-admin
On Fri, 2023-10-27 at 16:16 +0200, Loles wrote:
> Can the server be configured so that the default ORDER BY option is DESC instead of ASC?
>
> And to make it NULLS LAST instead of NULLS FIRST?
That is not simple.
You'd have to create new data types with new operators that do just the
opposite of the normal ones. I'd advise against it.
Yours,
Laurenz Albe
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: ORDER BY DESC and NULLS LAST by default
2023-10-27 14:16 ORDER BY DESC and NULLS LAST by default Loles <lolesft@gmail.com>
@ 2023-10-27 16:25 ` Holger Jakobs <holger@jakobs.com>
2023-10-27 17:55 ` Re: ORDER BY DESC and NULLS LAST by default Loles <lolesft@gmail.com>
2 siblings, 1 reply; 9+ messages in thread
From: Holger Jakobs @ 2023-10-27 16:25 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
Actually, the behaviour isn't NULLS LAST, but NULLS GREATEST.
There is no setting for choosing NULLS SMALLEST.
If you choose NULLS LAST, they always appear at the end, regardless of ASC or DESC. NULLS FIRST does the opposite.
Regards,
Holger
Am 27. Oktober 2023 16:16:09 MESZ schrieb Loles <lolesft@gmail.com>:
>Hi!
>
>Can the server be configured so that the default ORDER BY option is DESC
>instead of ASC?
>
>And to make it NULLS LAST instead of NULLS FIRST?
>
>Happy weekend and Thank you very much!
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: ORDER BY DESC and NULLS LAST by default
2023-10-27 14:16 ORDER BY DESC and NULLS LAST by default Loles <lolesft@gmail.com>
2023-10-27 16:25 ` Re: ORDER BY DESC and NULLS LAST by default Holger Jakobs <holger@jakobs.com>
@ 2023-10-27 17:55 ` Loles <lolesft@gmail.com>
2023-10-27 18:05 ` Re: ORDER BY DESC and NULLS LAST by default David G. Johnston <david.g.johnston@gmail.com>
2023-10-27 18:08 ` Re: ORDER BY DESC and NULLS LAST by default Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 9+ messages in thread
From: Loles @ 2023-10-27 17:55 UTC (permalink / raw)
To: pgsql-admin
I was really wondering if, just like you can disable autocommit mode in
PostgreSQL, if you could configure some parameter so that ORDER BY, by
default, would order DESC instead of ASC.
I suppose there is always the option of downloading the source code,
changing it, compiling and installing but it will undoubtedly be easier to
use ORDER BY DESC in all queries ;)
Thank you very much for all your contributions to my question.
Happy weekend!
El vie, 27 oct 2023 a las 18:25, Holger Jakobs (<holger@jakobs.com>)
escribió:
> Actually, the behaviour isn't NULLS LAST, but NULLS GREATEST.
>
> There is no setting for choosing NULLS SMALLEST.
>
> If you choose NULLS LAST, they always appear at the end, regardless of ASC
> or DESC. NULLS FIRST does the opposite.
>
> Regards,
>
> Holger
>
>
> Am 27. Oktober 2023 16:16:09 MESZ schrieb Loles <lolesft@gmail.com>:
>
>> Hi!
>>
>> Can the server be configured so that the default ORDER BY option is DESC
>> instead of ASC?
>>
>> And to make it NULLS LAST instead of NULLS FIRST?
>>
>> Happy weekend and Thank you very much!
>>
>
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: ORDER BY DESC and NULLS LAST by default
2023-10-27 14:16 ORDER BY DESC and NULLS LAST by default Loles <lolesft@gmail.com>
2023-10-27 16:25 ` Re: ORDER BY DESC and NULLS LAST by default Holger Jakobs <holger@jakobs.com>
2023-10-27 17:55 ` Re: ORDER BY DESC and NULLS LAST by default Loles <lolesft@gmail.com>
@ 2023-10-27 18:05 ` David G. Johnston <david.g.johnston@gmail.com>
1 sibling, 0 replies; 9+ messages in thread
From: David G. Johnston @ 2023-10-27 18:05 UTC (permalink / raw)
To: Loles <lolesft@gmail.com>; +Cc: pgsql-admin
On Fri, Oct 27, 2023 at 10:55 AM Loles <lolesft@gmail.com> wrote:
> I was really wondering if, just like you can disable autocommit mode in
> PostgreSQL
>
You cannot disable autocommit in the server...
David J.
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: ORDER BY DESC and NULLS LAST by default
2023-10-27 14:16 ORDER BY DESC and NULLS LAST by default Loles <lolesft@gmail.com>
2023-10-27 16:25 ` Re: ORDER BY DESC and NULLS LAST by default Holger Jakobs <holger@jakobs.com>
2023-10-27 17:55 ` Re: ORDER BY DESC and NULLS LAST by default Loles <lolesft@gmail.com>
@ 2023-10-27 18:08 ` Tom Lane <tgl@sss.pgh.pa.us>
2023-10-27 19:32 ` RE: ORDER BY DESC and NULLS LAST by default M Sarwar <sarwarmd02@outlook.com>
1 sibling, 1 reply; 9+ messages in thread
From: Tom Lane @ 2023-10-27 18:08 UTC (permalink / raw)
To: Loles <lolesft@gmail.com>; +Cc: pgsql-admin
Loles <lolesft@gmail.com> writes:
> I was really wondering if, just like you can disable autocommit mode in
> PostgreSQL, if you could configure some parameter so that ORDER BY, by
> default, would order DESC instead of ASC.
There is no such setting, and we'd be unlikely to consider adding one.
Bitter experience has taught us that GUC parameters that change query
behavior are usually bad ideas. Also, the SQL standard says
explicitly that ASC is the default behavior.
regards, tom lane
^ permalink raw reply [nested|flat] 9+ messages in thread
* RE: ORDER BY DESC and NULLS LAST by default
2023-10-27 14:16 ORDER BY DESC and NULLS LAST by default Loles <lolesft@gmail.com>
2023-10-27 16:25 ` Re: ORDER BY DESC and NULLS LAST by default Holger Jakobs <holger@jakobs.com>
2023-10-27 17:55 ` Re: ORDER BY DESC and NULLS LAST by default Loles <lolesft@gmail.com>
2023-10-27 18:08 ` Re: ORDER BY DESC and NULLS LAST by default Tom Lane <tgl@sss.pgh.pa.us>
@ 2023-10-27 19:32 ` M Sarwar <sarwarmd02@outlook.com>
0 siblings, 0 replies; 9+ messages in thread
From: M Sarwar @ 2023-10-27 19:32 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; Loles <lolesft@gmail.com>; +Cc: pgsql-admin
I agree on this approach!
Sarwar
Sent from my Galaxy
-------- Original message --------
From: Tom Lane <tgl@sss.pgh.pa.us>
Date: 10/27/23 2:08 PM (GMT-05:00)
To: Loles <lolesft@gmail.com>
Cc: pgsql-admin@postgresql.org
Subject: Re: ORDER BY DESC and NULLS LAST by default
Loles <lolesft@gmail.com> writes:
> I was really wondering if, just like you can disable autocommit mode in
> PostgreSQL, if you could configure some parameter so that ORDER BY, by
> default, would order DESC instead of ASC.
There is no such setting, and we'd be unlikely to consider adding one.
Bitter experience has taught us that GUC parameters that change query
behavior are usually bad ideas. Also, the SQL standard says
explicitly that ASC is the default behavior.
regards, tom lane
^ permalink raw reply [nested|flat] 9+ messages in thread
end of thread, other threads:[~2023-10-27 19:32 UTC | newest]
Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-10-27 14:16 ORDER BY DESC and NULLS LAST by default Loles <lolesft@gmail.com>
2023-10-27 14:17 ` M Sarwar <sarwarmd02@outlook.com>
2023-10-27 14:42 ` Arjan Saly Consultancy (EU) <consultancy@arjansaly.eu>
2023-10-27 15:54 ` Laurenz Albe <laurenz.albe@cybertec.at>
2023-10-27 16:25 ` Holger Jakobs <holger@jakobs.com>
2023-10-27 17:55 ` Loles <lolesft@gmail.com>
2023-10-27 18:05 ` David G. Johnston <david.g.johnston@gmail.com>
2023-10-27 18:08 ` Tom Lane <tgl@sss.pgh.pa.us>
2023-10-27 19:32 ` M Sarwar <sarwarmd02@outlook.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox