1.34.0

NullIf

Return null if a certain condition is met - for example, convert any empty strings to null:

from piccolo.query.functions import NullIf

class Venue(Table):
    address = Text()

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

Replace

String replacements within queries - for example, a simple slugify implementation:

from piccolo.query.functions import Replace

class Venue(Table):
    name = Varchar()

>>> await Venue.select(Replace(Venue.name, ' ', '-'))
[{'name': 'Amazing-Venue'}]

AtTimeZone

Convert Timestamptz columns from UTC to another timezone:

from piccolo.query.functions import AtTimeZone

class Signing(Table):
    starts = Timestamptz()

>>> await Signing.select(
...     AtTimeZone(Signing.starts, 'EST'),
... )
[{'starts': datetime.datetime(2026, 12, 20, 5, 0)}]

Char column

Similar to Varchar - useful if the strings are a fixed width.

class Venue(Table):
    country_code = Char(length=2)

It's also useful if using Piccolo on an existing database.

Fixture ordering

When dumping fixtures using piccolo fixtures dump, the data is now sorted by primary key, so subsequent dumps results in smaller Git diffs.

load_json with prefetch

When using .output(load_json=True) alongside prefetch, the child objects now load JSON strings into Python objects. Thanks to @diklios5768 for reporting this issue.

class RecordingStudio(Table):
    facilities = JSONB()

class Album(Table):
    recorded_at = ForeignKey(RecordingStudio)

>>> album = await Album.objects(Album.recorded_at).output(load_json=True).first()
>>> album.recorded_at.facilities
{
    'restaurant': True,
    'mixing_desk': True,
    'instruments': {'electric_guitars': 10, 'drum_kits': 2},
    'technicians': [{'name': 'Alice Jones'}, {'name': 'Bob Williams'}]
}

1.33.0

UUID v7

Added support for UUID v7.

from piccolo.columns.defaults.uuid import UUID7

class MyTable(Table):
    my_column = UUID(default=UUID7())

This provides superior insert performance and easier indexing compared to UUID v4.

Requires Python 3.14, and Postgres 18 (otherwise extensions are needed).

Coalesce

Added the Coalesce function - which lets you specify a fall back if a null value is found.

For example:

>>> await Album.select(
...     Coalesce(Album.release_date, datetime.date(2050, 1, 1))
... )

Or you can use this abbreviated syntax:

>>> await Album.select(
...     Album.release_date | datetime.date(2050, 1, 1)
... )

Other changes

• Fixed a bug with the Piccolo CLI, where custom names for commands weren't being applied (thanks to @sinisaos for this and @pelid for reporting the issue).
• Fixed typo in the get_related docstring (thanks to @nightcityblade for this).
• Fixed bugs with queries when a column has db_column_name defined (thanks to @VladislavYar for raising these issues).
• Improved the docs for Timestamp and Timestamptz columns.

1.32.0

Added the having clause, which is useful when working with group_by.

For example, here we get the number of albums per band, but exclude any bands with less than 2 albums:

>>> from piccolo.query.functions.aggregate import Count

>>> await Album.select(
...     Album.band.name.as_alias('band_name'),
...     Count()
... ).group_by(
...     Album.band
... ).having(
...     Count() >= 2
... )

[
    {"band_name": "Pythonistas", "count": 2},
]

We also updated our CockroachDB support to the latest version (thanks to @sinisaos for this).

1.31.0

• Added official Postgres 18 support.
• Removed graphlib backport (no longer needed in supported Python versions).
• Improved documentation for column kwargs.
• Updated README to mention other parts of the Piccolo ecosystem (thanks to @sinisaos for this).
• Table instances can now be compared using the == operator, and will return True if they have the same primary key value (thanks to @aarcex3 and @Skelmis for this).
• Fixed missing import for auto migrations when tables use LazyTableReference (thanks to @sinisaos for this).
• Improved docs for the auto generated primary key (thanks to @badlydrawnrob for this).
• Improved Table._table_str (this is most obvious in the playground, where the table definitions printed out now show more information - thanks to @badlydrawnrob for raising this issue).
• Fixed a bug with ModelBuilder when using Array columns with choices defined.
• add_m2m now returns the id of the joining table row, to match the docs (thanks to @diklios5768 for reporting this).
• Improved the docs for UUID columns (what the UUID4 default value does).
• Moved connection pooling to its own page in the docs.
• Improved the on_conflict clause - a target must be specified if using DO UPDATE (thanks to @mafuyuuu1 for raising this issue, and @sinisaos for this fix).
• Changed Esmerald to Ravyn in the ASGI templates (thanks to @sinisaos for this).
• Fixed a bug with auto migrations when changing a column to a foreign key - an exception was being raised (thanks to @gsavchuk for raising this issue and @sinisaos for this fix).
• Array(Numeric()) columns now work with SQLite (thanks to @sinisaos for this).
• UUID columns now use the built-in gen_random_uuid() function in Postgres to generate a default value, instead of using the uuid-ossp extension (thanks to @sinisaos for this).

1.30.0

Added support for Python 3.14 (thanks to @sinisaos for this).

1.29.0

• Fixed a bug with adding / subtracting Integer columns from one another in queries (thanks to @ryanvarley for this).
• Updated the ASGI templates, and BlackSheep dependencies (thanks to @sinisaos for this).
• Fixed a bug where decimal values generated by ModelBuilder could be too large.
• Added an example M2M relationship in the playground to make learning M2M easier (thanks to @sinisaos for this).
• Added documentation for MigrationManager.get_table_from_snapshot, which is a way of getting a Table from the migration history - useful when running data migrations (thanks to @sinisaos for this).
• Columns with the secret=True argument are now added to Table._meta.secret_columns (thanks to @sinisaos for this).
• Added documentation for the migration table.
• Tidied up Pydantic tests (thanks to @sinisaos for this).

1.28.0

Playground improvements

• Added an Array column to the playground (Album.awards), for easier experimentation with array columns.
• CoachroachDB is now supported in the playground (thanks to @sinisaos for this).

piccolo playground run --engine=cockroach

Functions

Added lots of useful array functions (thanks to @sinisaos for this).

Here's an example, where we can easily fix a typo in an array using replace:

>>> await Album.update({
...     Album.awards: Album.awards.replace('Grammy Award 2021', 'Grammy Award 2022')
... }, force=True)

The documentation for functions has also been improved (e.g. how to create a custom function).

The Cast function is now more flexible.

Array concantenation

Values can be prepended:

>>> await Album.update({
...     Album.awards: ['Grammy Award 2020'] + Album.awards
... }, force=True)

And multiple arrays can be concatenated in one go:

>>> await Album.update({
...     Album.awards: ['Grammy Award 2020'] + Album.awards + ['Grammy Award 2025']
... }, force=True)

is_in and not_in sub queries

You can now use sub queries within is_in and not_in Thanks to @sinisaos for this.

>>> await Band.select().where(
...     Band.id.is_in(
...         Concert.select(Concert.band_1).where(
...             Concert.starts >= datetime.datetime(year=2025, month=1, day=1)
...         )
...     )
... )

Other improvements

• Auto convert a default value of 0 to 0.0 in Float columns.
• Modernised the type hints throughout the codebase (e.g. using list instead of typing.List). Thanks to @sinisaos for this.
• Fixed a bug with auto migrations, where the Array base column class wasn't being imported.
• Improved M2M query performance by using sub selects (thanks to @sinisaos for this).

1.27.1

Improved the type annotations in ColumnKwargs - made some optional. Thanks to @stronk7 and @sinisaos for their help with this.

1.27.0

Improved auto completion / typo detection for column arguments.

For example:

  class Band(Table):
      name = Varchar(nul=True)  # linters will now warn that nul is a typo (should be null)

Thanks to @sinisaos for this.

1.26.1

Updated the BlackSheep ASGI template - thanks to @sinisaos for this.

Fixed a bug with auto migrations when a ForeignKey specifies target_column - multiple primary key columns were added to the migration file. Thanks to @waldner for reporting this issue.

Added a tutorial for moving tables between Piccolo apps - thanks to @sarvesh4396 for this.