django-postgres-extra

django-postgres-extra extends the Django ORM to provide native support for advanced PostgreSQL features. Unlike smaller, single-feature packages, this library provides well-tested implementations that integrate seamlessly with Django migrations, meaning you do not need to manually modify migration files to use its fields and objects.

Key capabilities include:

• Conflict Handling: Atomic, concurrency-safe upserts using PostgreSQL's ON CONFLICT syntax (supports DO UPDATE and DO NOTHING, including conditional updates).
• Table Partitioning: Support for PostgreSQL 11.x declarative table partitioning, fully integrated into Django migrations. Includes commands for automatic time-based partition creation.
• Views & Materialized Views: Ability to define views and materialized views as standard Django models, integrated into migrations.
• Locking: Support for explicit table-level locks.
• Schema Management: Tools for creating and dropping PostgreSQL schemas.
• Truncation: Support for TRUNCATE TABLE statements, including cascading deletes.
• Advanced Indexing:
  • Partial unique indices (conditional application).
  • Case-insensitive indices.
  • Multi-field unique indices.
• HStore Constraints: Unique and required constraints on specific hstore keys.

The PostgresManager provides support for PostgreSQL's ON CONFLICT clause, allowing for concurrency-safe inserts. You can specify whether to update an existing row (ConflictAction.UPDATE) or do nothing if a conflict occurs (ConflictAction.NOTHING).

Important: Standard Django methods like .create(), .update(), .get_or_create(), and .update_or_create() are not affected by .on_conflict(). You must use the specific psqlextra methods like .insert() or .insert_and_get() for the conflict logic to apply.

from django.db import models
from psqlextra.models import PostgresModel
from psqlextra.query import ConflictAction

class MyModel(PostgresModel):
    myfield = models.CharField(max_length=255, unique=True)

# Upsert: insert or update if already exists, then fetch the object
obj2 = (
    MyModel.objects
    .on_conflict(['myfield'], ConflictAction.UPDATE)
    .insert_and_get(myfield='beer')
)

# Do nothing: insert, or do nothing if it already exists, then fetch
obj1 = (
    MyModel.objects
    .on_conflict(['myfield'], ConflictAction.NOTHING)
    .insert_and_get(myfield='beer')
)

# Insert only: insert or update if already exists, then fetch only the primary key
id = (
    MyModel.objects
    .on_conflict(['myfield'], ConflictAction.UPDATE)
    .insert(myfield='beer')
)

PostgreSQL requires that the primary key is either the same as or includes the partitioning key. Behavior depends on your Django version:

Django < 5.2

• If the PK is the same as the partitioning key, standard behavior applies.
• If the PK is different or the partitioning key is composite, an implicit composite primary key is created (not visible to Django).

Django >= 5.2

• No explicit PK defined: A CompositePrimaryKey is automatically created including an auto-incrementing id field and the partitioning keys.
• Explicit CompositePrimaryKey defined: The library makes no modifications; you are responsible for ensuring partitioning keys are included in the definition.

Warning: Manually defining a pk using CompositePrimaryKey overrides the default behavior that includes an auto-incrementing id field.

# Custom composite primary key (overrides default auto-incrementing id)
class MyModel(PostgresPartitionedModel):
    class PartitioningMeta:
        method = PostgresPartitioningMethod.RANGE
        key = ["timestamp"]

    pk = models.CompositePrimaryKey("name", "timestamp")
    name = models.TextField()
    timestamp = models.DateTimeField()

The migration patches hook into Django's MigrationAutodetector and ProjectState to transform standard Django operations into Postgres-specific ones. This ensures that partitioning and view metadata (PartitioningMeta and ViewMeta) are preserved in your migration files.

Autodetector Transformations

The patch intercepts MigrationAutodetector.add_operation to perform the following mappings:

ProjectState Transformations

The patch intercepts ProjectState.from_apps to ensure custom model states are created, allowing the migration system to track specialized metadata:

• PostgresPartitionedModelState for PostgresPartitionedModel.
• PostgresViewModelState for PostgresViewModel.
• PostgresMaterializedViewModelState for PostgresMaterializedViewModel.