ProgrammingError: function lower(bigint) does not exist (Django, AWS RDS, PostgreSQL)

Question

In my Django Rest Framework project, I have a custom filter_backends that allows filtering by case insensitively:

class CaseInsensitiveOrderingFilter(OrderingFilter):
    
    def filter_queryset(self, request, queryset, view):
        ordering = self.get_ordering(request, queryset, view)
    
        if ordering:
            new_ordering = []
            for field in ordering:
             #   field = str(field)
                print(Lower(field))
                if field.startswith('-'):
                    new_ordering.append(Lower(field[1:]).desc())
                else:
                    new_ordering.append(Lower(field).asc())
            return queryset.order_by(*new_ordering)
    
        return queryset

This works fine in development.

Now I hosted the django app on elastic beanstalk and I configured a postgresql database via amazon relational database service (RDS).

When I try now to call the API, I get this error:

ProgrammingError at /api/profile_list/ function lower(bigint) does not exist LINE 1: ..."."author_id") GROUP BY "user_user"."id" ORDER BY LOWER(COUN...

HINT: No function matches the given name and argument types. You might need to add explicit type casts.

This error appears only in the RDS deployment.

I tried to type cast the fields in django with:

field = str(field)

But this is not working. Is there any way to allow caseinsensitive ordering without the lower function, or how can I conditionally check if it is a number (and cast then?) or a text abd

Why do you even need case insensitive ordering for a **number**? Clearly you want to be checking what your field actually is before using the `Lower` function. — Abdul Aziz Barkat, Mar 09 '21 at 18:06
I wonder why this code is working with my local db.sqlite, but not on RDS. How should I check the type before using Lower? — alexrogo, Mar 09 '21 at 18:09

Abdul Aziz Barkat · Accepted Answer · 2021-03-13T13:54:39.863

You get the error because you use Lower on a field which is perhaps an IntegerField or something else. You want to be checking what your field is before actually using Lower:

from django.db import models


def get_field_type(field_name, queryset):
    stripped_field_name = field_name.lstrip('-')
    if stripped_field_name in queryset.query.annotations:
        return queryset.query.annotations[stripped_field_name].output_field
    return queryset.model._meta.get_field(stripped_field_name)


class CaseInsensitiveOrderingFilter(OrderingFilter):
    
    def filter_queryset(self, request, queryset, view):
        ordering = self.get_ordering(request, queryset, view)
    
        if ordering:
            new_ordering = []
            for field in ordering:
                if not isinstance(get_field_type(field, queryset), (models.CharField, models.TextField)):
                    # Most of the character type fields inherit from CharField.
                    # I might miss a few fields here so you would need to make sure
                    new_ordering.append(field)
                elif field.startswith('-'):
                    new_ordering.append(Lower(field[1:]).desc())
                else:
                    new_ordering.append(Lower(field).asc())
            return queryset.order_by(*new_ordering)
    
        return queryset

How can I do this for additional fields that I declare in the serializer? Where I dont have a model type.. e.g. I save "number_of_authors" in Count(authors.id) — alexrogo, Mar 13 '21 at 13:00
@alexrogo I have edited the answer and added a function to get the fields type. Do check. — Abdul Aziz Barkat, Mar 13 '21 at 13:55

score 0 · Answer 2 · answered Feb 02 '23 at 14:35

To answer the additional question from this comment:

I wonder why this code is working with my local db.sqlite, but not on RDS. ...

The PostgreSQL lower function (as used on RDS) only accepts text values.

The SQLite lower function also accepts integers, although it is not explicitly mentioned in the docs.

This probably has to do with the fact that "SQLite uses a more general dynamic type system." Also see this SO answer.

This illustrates why it can be advantageous to use the same RDBMS both locally and in production.

NOTE:

Although SQLite does not complain when you do something like

... ORDER BY LOWER("my_integer_field")

the result can be surprising, because it will be in lexicographic order.

For example

[1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12]

will be sorted as text, resulting in

[1, 10, 11, 12, 2, 3, 4, 5, 6, 7, 8, 9]

ProgrammingError: function lower(bigint) does not exist (Django, AWS RDS, PostgreSQL)

2 Answers2