Обсуждение: ORDER BY DESC and NULLS LAST by default

Поиск
Список
Период
Сортировка

ORDER BY DESC and NULLS LAST by default

От
Loles
Дата:
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!

Re: ORDER BY DESC and NULLS LAST by default

От
M Sarwar
Дата:
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!

Re: ORDER BY DESC and NULLS LAST by default

От
"Arjan Saly Consultancy (EU)"
Дата:
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



Mail Signature.png

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!

Вложения

Re: ORDER BY DESC and NULLS LAST by default

От
Laurenz Albe
Дата:
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



Re: ORDER BY DESC and NULLS LAST by default

От
Holger Jakobs
Дата:
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!

Re: ORDER BY DESC and NULLS LAST by default

От
Loles
Дата:
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!

Re: ORDER BY DESC and NULLS LAST by default

От
"David G. Johnston"
Дата:
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.

Re: ORDER BY DESC and NULLS LAST by default

От
Tom Lane
Дата:
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



RE: ORDER BY DESC and NULLS LAST by default

От
M Sarwar
Дата:
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