API Reference

class psqlextra.manager.PostgresManager(*args, **kwargs)

Adds support for PostgreSQL specifics.

truncate(cascade: bool = False, using: str | None = None) → None

Truncates this model/table using the TRUNCATE statement.

This DELETES ALL ROWS. No signals will be fired.

See: https://www.postgresql.org/docs/9.1/sql-truncate.html

Parameters:: cascade – Whether to delete dependent rows. If set to False, an error will be raised if there are rows in other tables referencing the rows you’re trying to delete.

use_in_migrations = True: If set to True the manager will be serialized into migrations and will thus be available in e.g. RunPython operations.

class psqlextra.query.PostgresQuerySet(model=None, query=None, using=None, hints=None)

Adds support for PostgreSQL specifics.

bulk_insert(rows: Iterable[Dict[str, Any]], return_model: bool = False, using: str | None = None)

Creates multiple new records in the database.

This allows specifying custom conflict behavior using .on_conflict(). If no special behavior was specified, this uses the normal Django create(..)

Parameters:

rows – An iterable of dictionaries, where each dictionary describes the fields to insert.
(default (return_model) – False): If model instances should be returned rather than just dicts.
using – Optional name of the database connection to use for this query.

Returns:

A list of either the dicts of the rows inserted, including the pk or the models of the rows inserted with defaults for any fields not specified

Creates a set of new records or updates the existing ones with the specified data.

Parameters:

conflict_target – Fields to pass into the ON CONFLICT clause.
rows – Rows to upsert.
index_predicate – The index predicate to satisfy an arbiter partial index (i.e. what partial index to use for checking conflicts)
(default (return_model) – False): If model instances should be returned rather than just dicts.
using – The name of the database connection to use for this query.
update_condition – Only update if this SQL expression evaluates to true.
update_values – Optionally, values/expressions to use when rows conflict. If not specified, all columns specified in the rows are updated with the values you specified.

Returns:

A list of either the dicts of the rows upserted, including the pk or the models of the rows upserted

insert(using: str | None = None, **fields)

Creates a new record in the database.

This allows specifying custom conflict behavior using .on_conflict(). If no special behavior was specified, this uses the normal Django create(..)

Parameters:

fields – The fields of the row to create.
using – The name of the database connection to use for this query.

Returns:

The primary key of the record that was created.

insert_and_get(using: str | None = None, **fields)

Creates a new record in the database and then gets the entire row.

This allows specifying custom conflict behavior using .on_conflict(). If no special behavior was specified, this uses the normal Django create(..)

Parameters:

fields – The fields of the row to create.
using – The name of the database connection to use for this query.

Returns:

The model instance representing the row that was created.

Sets the action to take when conflicts arise when attempting to insert/create a new row.

Parameters:

fields – The fields the conflicts can occur in.
action – The action to take when the conflict occurs.
index_predicate – The index predicate to satisfy an arbiter partial index (i.e. what partial index to use for checking conflicts)
update_condition – Only update if this SQL expression evaluates to true.
update_values – Optionally, values/expressions to use when rows conflict. If not specified, all columns specified in the rows are updated with the values you specified.

Creates a new record or updates the existing one with the specified data.

Parameters:

conflict_target – Fields to pass into the ON CONFLICT clause.
fields – Fields to insert/update.
index_predicate – The index predicate to satisfy an arbiter partial index (i.e. what partial index to use for checking conflicts)
using – The name of the database connection to use for this query.
update_condition – Only update if this SQL expression evaluates to true.
update_values – Optionally, values/expressions to use when rows conflict. If not specified, all columns specified in the rows are updated with the values you specified.

Returns:

The primary key of the row that was created/updated.

Creates a new record or updates the existing one with the specified data and then gets the row.

Parameters:

conflict_target – Fields to pass into the ON CONFLICT clause.
fields – Fields to insert/update.
index_predicate – The index predicate to satisfy an arbiter partial index (i.e. what partial index to use for checking conflicts)
using – The name of the database connection to use for this query.
update_condition – Only update if this SQL expression evaluates to true.
update_values – Optionally, values/expressions to use when rows conflict. If not specified, all columns specified in the rows are updated with the values you specified.

Returns:

The model instance representing the row that was created/updated.

class psqlextra.models.PostgresMaterializedViewModel(*args, **kwargs)

Base class for creating a model that is a materialized view.

classmethod refresh(concurrently: bool = False, using: str | None = None) → None

Refreshes this materialized view.

Parameters:

concurrently – Whether to tell PostgreSQL to refresh this materialized view concurrently.
using – Optionally, the name of the database connection to use for refreshing the materialized view.

class psqlextra.models.PostgresModel(*args, **kwargs): Base class for for taking advantage of PostgreSQL specific features.

class psqlextra.models.PostgresPartitionedModel(*args, **kwargs): Base class for taking advantage of PostgreSQL’s 11.x native support for table partitioning.

class psqlextra.models.PostgresViewModel(*args, **kwargs): Base class for creating a model that is a view.

class psqlextra.fields.HStoreField(*args, uniqueness: List[str | Tuple[str, ...]] | None = None, required: List[str] | None = None, **kwargs)

Improved version of Django’s :see:HStoreField that adds support for database-level constraints.

Notes

For the implementation of uniqueness, see the custom database back-end.

__init__(*args, uniqueness: List[str | Tuple[str, ...]] | None = None, required: List[str] | None = None, **kwargs)

Initializes a new instance of :see:HStoreField.

Parameters:

uniqueness – List of keys to enforce as unique. Use tuples to enforce multiple keys together to be unique.
required – List of keys that should be enforced as required.

class psqlextra.expressions.HStoreRef(*args, **kwargs)

Inline reference to a HStore key.

Allows selecting individual keys in annotations.

class psqlextra.expressions.DateTimeEpoch(*args, **kwargs): Gets the date/time column as a UNIX epoch timestamp.

class psqlextra.expressions.ExcludedCol(*args, **kwargs)

References a column in PostgreSQL’s special EXCLUDED column, which is used in upserts to refer to the data about to be inserted/updated.

See: https://www.postgresql.org/docs/current/sql-insert.html#SQL-ON-CONFLICT

class psqlextra.indexes.UniqueIndex(*expressions, fields=(), name=None, db_tablespace=None, opclasses=(), condition=None, include=None)

class psqlextra.indexes.ConditionalUniqueIndex(condition: str, fields=[], name=None)

Creates a partial unique index based on a given condition.

Useful, for example, if you need unique combination of foreign keys, but you might want to include NULL as a valid value. In that case, you can just use:

>>> class Meta:
>>>    indexes = [
>>>        ConditionalUniqueIndex(fields=['a', 'b', 'c'], condition='"c" IS NOT NULL'),
>>>        ConditionalUniqueIndex(fields=['a', 'b'], condition='"c" IS NULL')
>>>    ]

class psqlextra.indexes.CaseInsensitiveUniqueIndex(*expressions, fields=(), name=None, db_tablespace=None, opclasses=(), condition=None, include=None)

class psqlextra.locking.PostgresTableLockMode(*values)

List of table locking modes.

See: https://www.postgresql.org/docs/current/explicit-locking.html

psqlextra.locking.postgres_lock_model(model: Type[Model], lock_mode: PostgresTableLockMode, *, using: str = 'default', schema_name: str | None = None) → None

Locks the specified model with the specified mode.

The lock is held until the end of the current transaction.

Parameters:

model – The model of which to lock the table.
lock_mode – Type of lock to acquire.
schema_name –
Optionally, the unquoted name of the schema the table to lock is in. If not specified, the table name is resolved by PostgreSQL using it’s search_path.

Django models always reside in the default (“public”) schema. You should not specify this unless you’re doing something special.
using – Optional name of the database connection to use.

psqlextra.locking.postgres_lock_table(table_name: str, lock_mode: PostgresTableLockMode, *, schema_name: str | None = None, using: str = 'default') → None

Locks the specified table with the specified mode.

The lock is held until the end of the current transaction.

Parameters:

table_name – Unquoted table name to acquire the lock on.
lock_mode – Type of lock to acquire.
schema_name – Optionally, the unquoted name of the schema the table to lock is in. If not specified, the table name is resolved by PostgreSQL using it’s search_path.
using – Optional name of the database connection to use.

class psqlextra.schema.PostgresSchema(name: str)

Represents a Postgres schema.

See: https://www.postgresql.org/docs/current/ddl-schemas.html

classmethod create(name: str, *, using: str = 'default') → PostgresSchema

Creates a new schema with the specified name.

This throws if the schema already exists as that is most likely a problem that requires careful handling. Pretending everything is ok might cause the caller to overwrite data, thinking it got a empty schema.

Parameters:

name – The name to give to the new schema (max 63 characters).
using – Optional name of the database connection to use.

classmethod create_random(prefix: str, *, using: str = 'default') → PostgresSchema

Creates a new schema with a random suffix.

Parameters:

prefix – Name to prefix the final name with. The name plus prefix cannot be longer than 63 characters.
using – Name of the database connection to use.

classmethod create_time_based(prefix: str, *, using: str = 'default') → PostgresSchema

Creates a new schema with a time-based suffix.

The time is precise up to the second. Creating multiple time based schema in the same second WILL lead to conflicts.

Parameters:

prefix – Name to prefix the final name with. The name plus prefix cannot be longer than 63 characters.
using – Name of the database connection to use.

delete(*, cascade: bool = False, using: str = 'default') → None

Deletes the schema and optionally deletes the contents of the schema and anything that references it.

Parameters:

cascade –

Cascade the delete to the contents of the schema and anything that references it.

If not set, the schema will refuse to be deleted unless it is empty and there are not remaining references.

classmethod delete_and_create(name: str, *, cascade: bool = False, using: str = 'default') → PostgresSchema

Deletes the schema if it exists before re-creating it.

Parameters:

name – Name of the schema to delete+create (max 63 characters).
cascade – Whether to delete the contents of the schema and anything that references it if it exists.
using – Optional name of the database connection to use.

classmethod exists(name: str, *, using: str = 'default') → bool

Gets whether a schema with the specified name exists.

Parameters:

name – Name of the schema to check of whether it exists.
using – Optional name of the database connection to use.

psqlextra.schema.postgres_temporary_schema(prefix: str, *, cascade: bool = False, delete_on_throw: bool = False, using: str = 'default') → Generator[PostgresSchema, None, None]

Creates a temporary schema that only lives in the context of this context manager.

Parameters:

prefix – Name to prefix the final name with.
cascade – Whether to cascade the delete when dropping the schema. If enabled, the contents of the schema are deleted as well as anything that references the schema.
delete_on_throw – Whether to automatically drop the schema if any error occurs within the context manager.
using – Optional name of the database connection to use.

class psqlextra.backend.migrations.operations.ApplyState(*args, **kwargs)

Takes an abritrary operation and migrates the project state but does not apply the operation to the database.

This is very similar to the :see:RunSQL state_operations parameter. This is useful if you want to tell Django that an operation was applied without actually applying it.

database_backwards(app_label, schema_editor, from_state, to_state): Perform the mutation on the database schema in the reverse direction - e.g. if this were CreateModel, it would in fact drop the model’s table.

database_forwards(app_label, schema_editor, from_state, to_state): Perform the mutation on the database schema in the normal (forwards) direction.

deconstruct(): Return a 3-tuple of class import path (or just name if it lives under django.db.migrations), positional arguments, and keyword arguments.

describe(): Output a brief summary of what the action does.

property reversible: Returns True when the argument is true, False otherwise. The builtins True and False are the only two instances of the class bool. The class bool is a subclass of the class int, and cannot be subclassed.

state_forwards(app_label, state): Take the state from the previous migration, and mutate it so that it matches what this migration would perform.

class psqlextra.backend.migrations.operations.PostgresAddDefaultPartition(*args, **kwargs)

Adds a new default partition to a :see:PartitionedPostgresModel.

database_backwards(app_label, schema_editor, from_state, to_state): Perform the mutation on the database schema in the reverse direction - e.g. if this were CreateModel, it would in fact drop the model’s table.

database_forwards(app_label, schema_editor, from_state, to_state): Perform the mutation on the database schema in the normal (forwards) direction.

describe() → str: Output a brief summary of what the action does.

state_forwards(app_label, state): Take the state from the previous migration, and mutate it so that it matches what this migration would perform.

class psqlextra.backend.migrations.operations.PostgresAddHashPartition(*args, **kwargs)

Adds a new hash partition to a :see:PartitionedPostgresModel.

Each partition will hold the rows for which the hash value of the partition key divided by the specified modulus will produce the specified remainder.

database_backwards(app_label, schema_editor, from_state, to_state): Perform the mutation on the database schema in the reverse direction - e.g. if this were CreateModel, it would in fact drop the model’s table.

database_forwards(app_label, schema_editor, from_state, to_state): Perform the mutation on the database schema in the normal (forwards) direction.

deconstruct(): Return a 3-tuple of class import path (or just name if it lives under django.db.migrations), positional arguments, and keyword arguments.

describe() → str: Output a brief summary of what the action does.

state_forwards(app_label, state): Take the state from the previous migration, and mutate it so that it matches what this migration would perform.

class psqlextra.backend.migrations.operations.PostgresAddListPartition(*args, **kwargs)

Adds a new list partition to a :see:PartitionedPostgresModel.

database_backwards(app_label, schema_editor, from_state, to_state): Perform the mutation on the database schema in the reverse direction - e.g. if this were CreateModel, it would in fact drop the model’s table.

database_forwards(app_label, schema_editor, from_state, to_state): Perform the mutation on the database schema in the normal (forwards) direction.

deconstruct(): Return a 3-tuple of class import path (or just name if it lives under django.db.migrations), positional arguments, and keyword arguments.

describe() → str: Output a brief summary of what the action does.

state_forwards(app_label, state): Take the state from the previous migration, and mutate it so that it matches what this migration would perform.

class psqlextra.backend.migrations.operations.PostgresAddRangePartition(*args, **kwargs)

Adds a new range partition to a :see:PartitionedPostgresModel.

database_backwards(app_label, schema_editor, from_state, to_state): Perform the mutation on the database schema in the reverse direction - e.g. if this were CreateModel, it would in fact drop the model’s table.

database_forwards(app_label, schema_editor, from_state, to_state): Perform the mutation on the database schema in the normal (forwards) direction.

deconstruct(): Return a 3-tuple of class import path (or just name if it lives under django.db.migrations), positional arguments, and keyword arguments.

describe() → str: Output a brief summary of what the action does.

state_forwards(app_label, state): Take the state from the previous migration, and mutate it so that it matches what this migration would perform.

class psqlextra.backend.migrations.operations.PostgresCreateMaterializedViewModel(*args, **kwargs)

Creates the model as a native PostgreSQL 11.x materialzed view.

database_backwards(app_label, schema_editor, from_state, to_state): Apply this migration operation backwards.

database_forwards(app_label, schema_editor, from_state, to_state): Apply this migration operation forwards.

deconstruct(): Return a 3-tuple of class import path (or just name if it lives under django.db.migrations), positional arguments, and keyword arguments.

describe(): Gets a human readable text describing this migration.

state_forwards(app_label, state): Take the state from the previous migration, and mutate it so that it matches what this migration would perform.

class psqlextra.backend.migrations.operations.PostgresCreatePartitionedModel(*args, **kwargs)

Creates the model as a native PostgreSQL 11.x partitioned table.

database_backwards(app_label, schema_editor, from_state, to_state): Apply this migration operation backwards.

database_forwards(app_label, schema_editor, from_state, to_state): Apply this migration operation forwards.

deconstruct(): Return a 3-tuple of class import path (or just name if it lives under django.db.migrations), positional arguments, and keyword arguments.

describe(): Gets a human readable text describing this migration.

reduce(*args, **kwargs): Return either a list of operations the actual operation should be replaced with or a boolean that indicates whether or not the specified operation can be optimized across.

state_forwards(app_label, state): Take the state from the previous migration, and mutate it so that it matches what this migration would perform.

class psqlextra.backend.migrations.operations.PostgresCreateViewModel(*args, **kwargs)

Creates the model as a native PostgreSQL 11.x view.

database_backwards(app_label, schema_editor, from_state, to_state): Apply this migration operation backwards.

database_forwards(app_label, schema_editor, from_state, to_state): Apply this migration operation forwards.

deconstruct(): Return a 3-tuple of class import path (or just name if it lives under django.db.migrations), positional arguments, and keyword arguments.

describe(): Gets a human readable text describing this migration.

state_forwards(app_label, state): Take the state from the previous migration, and mutate it so that it matches what this migration would perform.

class psqlextra.backend.migrations.operations.PostgresDeleteDefaultPartition(*args, **kwargs)

Deletes a default partition that’s part of a.

:see:PartitionedPostgresModel.

database_backwards(app_label, schema_editor, from_state, to_state): Perform the mutation on the database schema in the reverse direction - e.g. if this were CreateModel, it would in fact drop the model’s table.

describe() → str: Output a brief summary of what the action does.

class psqlextra.backend.migrations.operations.PostgresDeleteHashPartition(*args, **kwargs)

Deletes a hash partition that’s part of a.

:see:PartitionedPostgresModel.

database_backwards(app_label, schema_editor, from_state, to_state): Perform the mutation on the database schema in the reverse direction - e.g. if this were CreateModel, it would in fact drop the model’s table.

describe() → str: Output a brief summary of what the action does.

class psqlextra.backend.migrations.operations.PostgresDeleteListPartition(*args, **kwargs)

Deletes a list partition that’s part of a.

:see:PartitionedPostgresModel.

database_backwards(app_label, schema_editor, from_state, to_state): Perform the mutation on the database schema in the reverse direction - e.g. if this were CreateModel, it would in fact drop the model’s table.

describe() → str: Output a brief summary of what the action does.

class psqlextra.backend.migrations.operations.PostgresDeleteMaterializedViewModel(*args, **kwargs)

Deletes the specified materialized view model.

database_backwards(app_label, schema_editor, from_state, to_state): Apply this migration operation backwards.

database_forwards(app_label, schema_editor, from_state, to_state): Apply this migration operation forwards.

describe(): Gets a human readable text describing this migration.

class psqlextra.backend.migrations.operations.PostgresDeletePartitionedModel(*args, **kwargs)

Deletes the specified partitioned model.

database_backwards(app_label, schema_editor, from_state, to_state): Apply this migration operation backwards.

database_forwards(app_label, schema_editor, from_state, to_state): Apply this migration operation forwards.

describe(): Gets a human readable text describing this migration.

class psqlextra.backend.migrations.operations.PostgresDeleteRangePartition(*args, **kwargs)

Deletes a range partition that’s part of a.

:see:PartitionedPostgresModel.

database_backwards(app_label, schema_editor, from_state, to_state): Perform the mutation on the database schema in the reverse direction - e.g. if this were CreateModel, it would in fact drop the model’s table.

describe() → str: Output a brief summary of what the action does.

class psqlextra.backend.migrations.operations.PostgresDeleteViewModel(*args, **kwargs)

Deletes the specified view model.

database_backwards(app_label, schema_editor, from_state, to_state): Apply this migration operation backwards.

database_forwards(app_label, schema_editor, from_state, to_state): Apply this migration operation forwards.

describe(): Gets a human readable text describing this migration.

class psqlextra.types.ConflictAction(*values)

Possible actions to take on a conflict.

NOTHING = 'NOTHING'

UPDATE = 'UPDATE'

classmethod all() → List[ConflictAction]

class psqlextra.types.PostgresPartitioningMethod(*values)

Methods of partitioning supported by PostgreSQL 11.x native support for table partitioning.

HASH = 'hash'

LIST = 'list'

RANGE = 'range'

class psqlextra.types.StrEnum(new_class_name, /, names, *, module=None, qualname=None, type=None, start=1, boundary=None)

classmethod all() → List[StrEnum]

classmethod values() → List[str]

psqlextra.util.postgres_manager(model: Type[Model]) → Generator[PostgresManager, None, None]

Allows you to use the :see:PostgresManager with the specified model instance on the fly.

Parameters:: model – The model or model instance to use this on.