Django queryset place of None/Null in orderby in Postgresql

Question

I have a queryset that I am ordering by -DateTimeField

model:

class QuestSubmission(models.Model):
    quest = models.ForeignKey(Quest)
    user = models.ForeignKey(settings.AUTH_USER_MODEL, 
    ...
    time_returned = models.DateTimeField(null=True, blank=True)

queryset:

QuestSubmission.objects.all().order_by('-time_returned')

In my sqlite3 tests, this order newest to oldest followed by None/Null entries

But on my Postgresql server the order is None/Null then newest to oldest.

How can I get my postgresql order_by to place the None/Null values at the end similar to the sqlite3?

This looks like a duplicate of stackoverflow.com/questions/15121093/… — solarissmoke
– solarissmoke, Commented Oct 16, 2015 at 4:43

Mark Chackerian · Accepted Answer · 2020-07-21 04:19:53Z

1

Something like this should work.

select * from dude order by case when date is null then 1 else 0 end, date desc;
 test  |    date    
-------+------------
 test2 | 2001-01-04
 test2 | 2001-01-03
 test2 | 2001-01-02
 test2 | 2001-01-01
 test  | 
 test2 |

example:

from django.db.models import Case, When, Value, IntegerField

SomeModel.objects.annotate(
    nulls_last=Case(
        When(time_returned__isnull=True, then=Value(1)),
        When(time_returned__isnull=False, then=Value(0)),
        output_field=IntegerField(),
    )
).order_by('nulls_last', '-time_returned')

from here: Django order_by specific order

edited Jul 21, 2020 at 4:19

Mark Chackerian

23.8k7 gold badges116 silver badges106 bronze badges

answered Oct 16, 2015 at 5:16

Javier Buzzi

6,9931 gold badge44 silver badges56 bronze badges

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

Collectives™ on Stack Overflow

Django queryset place of None/Null in orderby in Postgresql

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related