The classes documented below provide a way for users to use functions provided by the underlying database as annotations, aggregations, or filters in Django. Functions are also expressions, so they can be used and combined with other expressions like aggregate functions.
We’ll be using the following model in examples of each function:
class Author(models.Model):
name = models.CharField(max_length=50)
age = models.PositiveIntegerField(null=True, blank=True)
alias = models.CharField(max_length=50, null=True, blank=True)
goes_by = models.CharField(max_length=50, null=True, blank=True)
We don’t usually recommend allowing null=True for CharField since this
allows the field to have two “empty values”, but it’s important for the
Coalesce example below.
Coalesce¶Accepts a list of at least two field names or expressions and returns the first non-null value (note that an empty string is not considered a null value). Each argument must be of a similar type, so mixing text and numbers will result in a database error.
Usage examples:
>>> # Get a screen name from least to most public
>>> from django.db.models import Sum, Value as V
>>> from django.db.models.functions import Coalesce
>>> Author.objects.create(name='Margaret Smith', goes_by='Maggie')
>>> author = Author.objects.annotate(
... screen_name=Coalesce('alias', 'goes_by', 'name')).get()
>>> print(author.screen_name)
Maggie
>>> # Prevent an aggregate Sum() from returning None
>>> aggregated = Author.objects.aggregate(
... combined_age=Coalesce(Sum('age'), V(0)),
... combined_age_default=Sum('age'))
>>> print(aggregated['combined_age'])
0
>>> print(aggregated['combined_age_default'])
None
Warning
A Python value passed to Coalesce on MySQL may be converted to an
incorrect type unless explicitly cast to the correct database type:
>>> from django.db.models.expressions import RawSQL
>>> from django.utils import timezone
>>> now = timezone.now()
>>> now_sql = RawSQL("cast(%s as datetime)", (now,))
>>> Coalesce('updated', now_sql)
Concat¶Accepts a list of at least two text fields or expressions and returns the
concatenated text. Each argument must be of a text or char type. If you want
to concatenate a TextField() with a CharField(), then be sure to tell
Django that the output_field should be a TextField(). This is also
required when concatenating a Value as in the example below.
This function will never have a null result. On backends where a null argument results in the entire expression being null, Django will ensure that each null part is converted to an empty string first.
Usage example:
>>> # Get the display name as "name (goes_by)"
>>> from django.db.models import CharField, Value as V
>>> from django.db.models.functions import Concat
>>> Author.objects.create(name='Margaret Smith', goes_by='Maggie')
>>> author = Author.objects.annotate(
... screen_name=Concat('name', V(' ('), 'goes_by', V(')'),
... output_field=CharField())).get()
>>> print(author.screen_name)
Margaret Smith (Maggie)
Greatest¶Accepts a list of at least two field names or expressions and returns the greatest value. Each argument must be of a similar type, so mixing text and numbers will result in a database error.
Usage example:
class Blog(models.Model):
body = models.TextField()
modified = models.DateTimeField(auto_now=True)
class Comment(models.Model):
body = models.TextField()
modified = models.DateTimeField(auto_now=True)
blog = models.ForeignKey(Blog, on_delete=models.CASCADE)
>>> from django.db.models.functions import Greatest
>>> blog = Blog.objects.create(body='Greatest is the best.')
>>> comment = Comment.objects.create(body='No, Least is better.', blog=blog)
>>> comments = Comment.objects.annotate(last_updated=Greatest('modified', 'blog__modified'))
>>> annotated_comment = comments.get()
annotated_comment.last_updated will be the most recent of blog.modified
and comment.modified.
Warning
The behavior of Greatest when one or more expression may be null
varies between databases:
Greatest will return the largest non-null expression,
or null if all expressions are null.null, Greatest
will return null.The PostgreSQL behavior can be emulated using Coalesce if you know
a sensible minimum value to provide as a default.
Least¶Accepts a list of at least two field names or expressions and returns the least value. Each argument must be of a similar type, so mixing text and numbers will result in a database error.
Warning
The behavior of Least when one or more expression may be null
varies between databases:
Least will return the smallest non-null expression,
or null if all expressions are null.null, Least
will return null.The PostgreSQL behavior can be emulated using Coalesce if you know
a sensible maximum value to provide as a default.
Length¶Accepts a single text field or expression and returns the number of characters the value has. If the expression is null, then the length will also be null.
Usage example:
>>> # Get the length of the name and goes_by fields
>>> from django.db.models.functions import Length
>>> Author.objects.create(name='Margaret Smith')
>>> author = Author.objects.annotate(
... name_length=Length('name'),
... goes_by_length=Length('goes_by')).get()
>>> print(author.name_length, author.goes_by_length)
(14, None)
It can also be registered as a transform. For example:
>>> from django.db.models import CharField
>>> from django.db.models.functions import Length
>>> CharField.register_lookup(Length, 'length')
>>> # Get authors whose name is longer than 7 characters
>>> authors = Author.objects.filter(name__length__gt=7)
The ability to register the function as a transform was added.
Lower¶Accepts a single text field or expression and returns the lowercase representation.
It can also be registered as a transform as described in Length.
Usage example:
>>> from django.db.models.functions import Lower
>>> Author.objects.create(name='Margaret Smith')
>>> author = Author.objects.annotate(name_lower=Lower('name')).get()
>>> print(author.name_lower)
margaret smith
The ability to register the function as a transform was added.
Now¶Returns the database server’s current date and time when the query is executed,
typically using the SQL CURRENT_TIMESTAMP.
Usage example:
>>> from django.db.models.functions import Now
>>> Article.objects.filter(published__lte=Now())
[<Article: How to Django>]
PostgreSQL considerations
On PostgreSQL, the SQL CURRENT_TIMESTAMP returns the time that the
current transaction started. Therefore for cross-database compatibility,
Now() uses STATEMENT_TIMESTAMP instead. If you need the transaction
timestamp, use django.contrib.postgres.functions.TransactionNow.
Substr¶Returns a substring of length length from the field or expression starting
at position pos. The position is 1-indexed, so the position must be greater
than 0. If length is None, then the rest of the string will be returned.
Usage example:
>>> # Set the alias to the first 5 characters of the name as lowercase
>>> from django.db.models.functions import Substr, Lower
>>> Author.objects.create(name='Margaret Smith')
>>> Author.objects.update(alias=Lower(Substr('name', 1, 5)))
1
>>> print(Author.objects.get(name='Margaret Smith').alias)
marga
Upper¶Accepts a single text field or expression and returns the uppercase representation.
It can also be registered as a transform as described in Length.
Usage example:
>>> from django.db.models.functions import Upper
>>> Author.objects.create(name='Margaret Smith')
>>> author = Author.objects.annotate(name_upper=Upper('name')).get()
>>> print(author.name_upper)
MARGARET SMITH
The ability to register the function as a transform was added.
May 02, 2016