How to change default null sorting behavior from PostgreSQL in the Django ORM

Question

PostgreSQL by default considers NULL values as the highest, and thus sorts them first for descending queries and last for ascending ones.

You can modify this behaviour per query or at index creation by specifying 'NULLS LAST' or 'NULLS FIRST'.

How can I use this in conjunction with the Django ORM, without needing to use raw queries? I.e. when I add to my query_set something like qs.order_by("-publish_start"), how can I specify sorting for nulls? Or, as an alternative, upon field/index declaration.

Carles Barrobés · Accepted Answer · 2012-09-27 23:32:09Z

10

I figured out a way that accommodates DB engines that work either way (null as highest or lowest value) by using extra, making the null check a boolean, and when sorting booleans false < true seems to be universal:

qs = qs.extra(select={'null_start': "publish_start is null"},
              order_by=['null_start', '-publish_start'])

answered Sep 27, 2012 at 23:32

Carles Barrobés

11.7k5 gold badges50 silver badges61 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Phil Gyford Over a year ago

It's worth stating (because I'm slow and it took me a while to figure out) that if you want the results to be with NULL values first, and then publish_start ordered ASCending, you need to reverse the order_by statement: ['-null_start', 'publish_start'].

Carles Barrobés Over a year ago

@blueyed yes that is what 4-year old answers in stack overflow have. They grow obsolete. No need to downvote for that...

Collectives™ on Stack Overflow

How to change default null sorting behavior from PostgreSQL in the Django ORM

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related