Thread: configure postgtresql to order NULLS FIRST instead of the defaultNULLS LAST
Re: configure postgtresql to order NULLS FIRST instead of the defaultNULLS LAST
is there a setting that is available for me to use to configure Postgresql to order NULLS FIRST by default instead of NULLS LAST?
Re: configure postgtresql to order NULLS FIRST instead of the defaultNULLS LAST
On 04/01/2019 08:09, David G. Johnston wrote: > On Thursday, January 3, 2019, john snow <ofbizfanster@gmail.com > <mailto:ofbizfanster@gmail.com>> wrote: > > is there a setting that is available for me to use to configure > Postgresql to order NULLS FIRST by default instead of NULLS LAST? > > > No there is not and likely never will be as such an option introduces > undesirable variability. That, and the spec forbids it. "If <null ordering> is not specified, then an implementation-defined <null ordering> is implicit. The implementation-defined default for <null ordering> shall not depend on the context outside of <sort specification list>." -- Vik Fearing +33 6 46 75 15 36 http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support
Re: configure postgtresql to order NULLS FIRST instead of thedefault NULLS LAST
From: Vik Fearing <vik.fearing@2ndquadrant.com>
To: David G. Johnston <david.g.johnston@gmail.com>; john snow <ofbizfanster@gmail.com>
Cc: pgsql-novice <pgsql-novice@postgresql.org>
Sent: Friday, January 4, 2019 1:33 AM
Subject: Re: configure postgtresql to order NULLS FIRST instead of the default NULLS LAST
> On Thursday, January 3, 2019, john snow <ofbizfanster@gmail.com
> <mailto:ofbizfanster@gmail.com>> wrote:
>
> is there a setting that is available for me to use to configure
> Postgresql to order NULLS FIRST by default instead of NULLS LAST?
>
>
> No there is not and likely never will be as such an option introduces
> undesirable variability.
That, and the spec forbids it.
"If <null ordering> is not specified, then an implementation-defined
<null ordering> is implicit. The implementation-defined default for
<null ordering> shall not depend on the context outside of <sort
specification list>."
--
Vik Fearing +33 6 46 75 15 36
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support
RE: configure postgtresql to order NULLS FIRST instead of the defaultNULLS LAST
According to the documentation on SELECT, this can be done a per query basis using the ORDER BY … NULLS FIRST/LAST clause. This syntax was first added in version 8.3 (rather than using a CASE statement), but it is still per query, and not globally on the server.
https://www.postgresql.org/docs/current/sql-select.html#SQL-ORDERBY
> If NULLS LAST
is specified, null values sort after all non-null values; if NULLS FIRST
is specified, null values sort before all non-null values. If neither is specified, the default behavior is NULLS LAST
when ASC
is specified or implied, and NULLS FIRST
when DESC
is specified (thus, the default is to act as though nulls are larger than non-nulls). When USING
is specified, the default nulls ordering depends on whether the operator is a less-than or greater-than operator.
There is also syntax that supports using your own operator. If all of your data that is likely to be ordered is of a user-defined data type then you could try creating your own types, domains, and operators that treat nulls however you want, but that is non-trivial and would only apply automatically to columns of those custom data types, and not standard data types like INTEGER, TEXT, etc.
> Alternatively, a specific ordering operator name can be specified in the USING clause. An ordering operator must be a less-than or greater-than member of some B-tree operator family. ASC is usually equivalent to USING < and DESC is usually equivalent to USING >. (But the creator of a user-defined data type can define exactly what the default sort ordering is, and it might correspond to operators with other names.)
Of course you can work on training users and developers on the use of NULLS FIRST/LAST. Or you could create views to do complex sorting on a table and have users use the view instead of the table, but that is still extra development per table, not your apparently desired global change of behavior.
any attachments are intended solely for the
addressee(s) and may contain confidential
and/or legally privileged information. If you
are not the intended recipient of this message
or if this message has been addressed to you
in error, please immediately alert the sender
by reply e-mail and then delete this message
and any attachments. If you are not the
intended recipient, you are notified that
any use, dissemination, distribution, copying,
or storage of this message or any attachment
is strictly prohibited.
Re: configure postgtresql to order NULLS FIRST instead of the defaultNULLS LAST
Or you could create views to do complex sorting on a table and have users use the view instead of the table, but that is still extra development per table, not your apparently desired global change of behavior.