Conditional functions

Coalesce

class piccolo.query.functions.conditional.Coalesce(*args: Column | QueryString | BasicTypes, alias: str | None = None)

Returns the first non-null value.

Here’s an example to try in the playground:

>>> await Album.select(Album.release_date)
[
    {'release_date': datetime.date(2021, 1, 1)},
    {'release_date': datetime.date(2025, 1, 1)},
    {'release_date': datetime.date(2022, 2, 2)},
    {'release_date': None}
]

One of the values is null - we can specify a fallback value:

>>> from piccolo.functions.conditional import Coalesce
>>> await Album.select(
...     Coalesce(Album.release_date, datetime.date(2050, 1, 1))
... )
[
    {'release_date': datetime.date(2021, 1, 1)},
    {'release_date': datetime.date(2025, 1, 1)},
    {'release_date': datetime.date(2022, 2, 2)},
    {'release_date': datetime.date(2050, 1, 1)}
]

Or us this abbreviated syntax:

>>> await Album.select(
...     Album.release_date | datetime.date(2050, 1, 1)
... )
[
    {'release_date': datetime.date(2021, 1, 1)},
    {'release_date': datetime.date(2025, 1, 1)},
    {'release_date': datetime.date(2022, 2, 2)},
    {'release_date': datetime.date(2050, 1, 1)}
]

NullIf

class piccolo.query.functions.conditional.NullIf(identifier: Column | QueryString, value: BasicTypes | QueryString, alias: str | None = None)

Returns null if the value in the database equals value.

An example is where a Varchar or Text column contains a mix of empty strings and null. We might want to standardise the response so it’s just null.

For example:

class Venue(Table):
    name = Varchar()
    address = Text(null=True)

>>> await Venue.select(Venue.name, NullIf(Venue.address, ''))
[{'name': 'Amazing venue', 'address': None}]