Обсуждение: 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!
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
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!
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
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
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.SarwarFrom: 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 defaultHi!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!
Вложения
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
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
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!
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,
HolgerAm 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!
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.
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
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
> 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