### Install uv

Source: https://github.com/sectorlabs/django-postgres-extra/blob/master/README.md

Install uv, a fast Python package installer and resolver, if you don't have it already.

```bash
λ curl -LsSf https://astral.sh/uv/install.sh | sh
```

--------------------------------

### Install django-postgres-extra via Pip

Source: https://github.com/sectorlabs/django-postgres-extra/blob/master/docs/source/installation.rst

Use pip to install the package from PyPI. This is the first step in the installation process.

```bash
$ pip install django-postgres-extra
```

--------------------------------

### Sync Dependencies

Source: https://github.com/sectorlabs/django-postgres-extra/blob/master/README.md

Sync development and test dependencies using uv. This command creates a virtual environment named .venv and installs packages from uv.lock.

```bash
λ cd django-postgres-extra
λ uv sync --extra dev --extra test
λ source .venv/bin/activate
```

--------------------------------

### Insert or Update/Nothing and Get

Source: https://github.com/sectorlabs/django-postgres-extra/blob/master/docs/source/conflict_handling.rst

Demonstrates inserting a row and either updating it if a conflict occurs or doing nothing, then fetching the resulting object. Requires unique constraints on 'myfield'.

```python
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)

# insert or update if already exists, then fetch, all in a single query
obj2 = (
    MyModel.objects
    .on_conflict(['myfield'], ConflictAction.UPDATE)
    .insert_and_get(myfield='beer')
)

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

--------------------------------

### Insert or Update and Get Primary Key

Source: https://github.com/sectorlabs/django-postgres-extra/blob/master/docs/source/conflict_handling.rst

Shows how to insert a row, updating it if a conflict occurs, and then fetching only the primary key. Requires unique constraints on 'myfield'.

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

--------------------------------

### Bulk Insert with Different Column Configurations (Invalid)

Source: https://github.com/sectorlabs/django-postgres-extra/blob/master/docs/source/conflict_handling.rst

This example demonstrates an invalid usage of bulk_insert where rows have different column configurations. psqlextra will throw an exception in such cases to maintain the 'everything in one query' principle.

```python
from django.db import models
from psqlextra.models import PostgresModel

class MyModel(PostgresModel):
    first_name = models.CharField(max_length=255, unique=True)
    last_name = models.CharField(max_length=255, default='kooij')

obj = (
    MyModel.objects
    .on_conflict(['name'], ConflictAction.UPDATE)
    .bulk_insert([
        dict(name='swen'),
        dict(name='henk', last_name='poepjes'), # invalid, different column configuration
        dict(name='adela')
    ])
)
```

--------------------------------

### Truncate Table with Restart Identity

Source: https://github.com/sectorlabs/django-postgres-extra/blob/master/docs/source/deletion.rst

Use restart_identity=True to reset any sequences associated with the table after truncation. This ensures new records get sequential IDs starting from 1.

```python
from django.db import models
from psqlextra.models import PostgresModel

class MyModel(PostgresModel):
   pass

mymodel = MyModel.objects.create()
assert mymodel.id == 1

MyModel.objects.truncate(restart_identity=True) # table is empty after this
print(MyModel.objects.count()) # zero records left

# Create a new row, it should get ID 1 again because
# the sequence got restarted.
mymodel = MyModel.objects.create()
assert mymodel.id == 1
```

--------------------------------

### Truncate with Restart Identity

Source: https://github.com/sectorlabs/django-postgres-extra/blob/master/docs/source/deletion.rst

The `truncate` method can be used with the `restart_identity=True` option to restart any sequences associated with the table, ensuring new records get sequential IDs starting from 1.

```APIDOC
## Truncate with Restart Identity

### Description
Truncates a table and restarts its associated sequences by setting `restart_identity=True`.

### Method
`PostgresManager.truncate(restart_identity=True)`

### Parameters
*   **restart_identity** (bool) - Optional - If `True`, any sequences on the table will be restarted.

### Request Example
```python
from django.db import models
from psqlextra.models import PostgresModel

class MyModel(PostgresModel):
   pass

mymodel = MyModel.objects.create()
assert mymodel.id == 1

MyModel.objects.truncate(restart_identity=True) # table is empty after this
print(MyModel.objects.count()) # zero records left

# Create a new row, it should get ID 1 again because
# the sequence got restarted.
mymodel = MyModel.objects.create()
assert mymodel.id == 1
```

### Response
#### Success Response
This method does not return a value, but the table will be empty and sequences restarted.

#### Response Example
```
0
1
```
```

--------------------------------

### Upsert and Get Single Row (Shorthand)

Source: https://github.com/sectorlabs/django-postgres-extra/blob/master/docs/source/conflict_handling.rst

Use the shorthand upsert_and_get method for inserting or updating a single row, returning the primary key. This is an older method equivalent to ConflictAction.UPDATE.

```python
from django.db import models
from psqlextra.models import PostgresModel

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

obj = (
    MyModel.objects
    .upsert_and_get(
        conflict_target=['myfield']
        fields=dict(myfield='beer')
    )
)
```

--------------------------------

### Create a Conditional Unique Index

Source: https://github.com/sectorlabs/django-postgres-extra/blob/master/docs/source/indexes.rst

ConditionalUniqueIndex allows for partial unique indexes, useful for nullable columns where `unique_together` constraints are needed. The 'Before' example shows a standard `unique_together` that allows multiple nulls. The 'After' example uses ConditionalUniqueIndex to enforce uniqueness only when the 'a' field is not null, and a separate index for when 'a' is null.

```python
from django.db import models

class Model(models.Model):
    class Meta:
        unique_together = ['a', 'b']

    a = models.ForeignKey('some_model', null=True)
    b = models.ForeignKey('some_other_model')

# Works like a charm!
b = B()
Model.objects.create(a=None, b=b)
Model.objects.create(a=None, b=b)

```

```python
from django.db import models
from psqlextra.indexes import ConditionalUniqueIndex

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

    a = models.ForeignKey('some_model', null=True)
    b = models.ForeignKey('some_other_model')

# Integrity Error!
b = B()
Model.objects.create(a=None, b=b)
Model.objects.create(a=None, b=b)

```

--------------------------------

### Select datetime as UNIX epoch timestamp

Source: https://github.com/sectorlabs/django-postgres-extra/blob/master/docs/source/expressions.rst

Use DateTimeEpoch to get a datetime field's value as a UNIX epoch timestamp. Requires a DateTimeField in the model.

```python
from psqlextra.models import PostgresModel
from psqlextra.fields import HStoreField
from psqlextra.expressions import DateTimeEpoch

class MyModel(PostgresModel):
   datetime = DateTimeField(auto_now_add=True)

MyModel.objects.create()

timestamp = (
   MyModel.objects
   .annotate(timestamp=DateTimeEpoch('datetime'))
   .values_list('timestamp', flat=True)
   .first()
)
```

--------------------------------

### Add Range Partition using Migration Operation

Source: https://github.com/sectorlabs/django-postgres-extra/blob/master/docs/source/table_partitioning.rst

Use PostgresAddRangePartition to add a new range partition to a model configured for RANGE partitioning. Specify the model name, partition name, and the start and end values for the range.

```python
from django.db import migrations, models

from psqlextra.backend.migrations.operations import PostgresAddRangePartition

class Migration(migrations.Migration):
    operations = [
        PostgresAddRangePartition(
           model_name="mypartitionedmodel",
           name="pt1",
           from_values="2019-01-01",
           to_values="2019-02-01",
        ),
    ]
```

--------------------------------

### Run Benchmarks

Source: https://github.com/sectorlabs/django-postgres-extra/blob/master/README.md

Run the project's benchmarks using the 'poe benchmark' command.

```bash
λ poe benchmark
```

--------------------------------

### Run Tests

Source: https://github.com/sectorlabs/django-postgres-extra/blob/master/README.md

Execute the project's test suite using the 'poe test' command.

```bash
λ poe test
```

--------------------------------

### Configure PostgresPartitioningManager

Source: https://github.com/sectorlabs/django-postgres-extra/blob/master/docs/source/table_partitioning.rst

Set the 'PSQLEXTRA_PARTITIONING_MANAGER' setting to the import path of your 'PostgresPartitioningManager' instance to enable automatic partition management.

```python
# myapp/partitioning.py
from psqlextra.partitioning import PostgresPartitioningManager

manager = PostgresPartitioningManager(...)

# myapp/settings.py
PSQLEXTRA_PARTITIONING_MANAGER = 'myapp.partitioning.manager'
```

--------------------------------

### Configure Time-Based Partitioning Strategy

Source: https://github.com/sectorlabs/django-postgres-extra/blob/master/docs/source/table_partitioning.rst

Set up multiple time-based partitioning strategies for a model, defining partition size, count, and maximum age. Partitions are automatically named based on the table name and time components.

```python
from dateutil.relativedelta import relativedelta

from psqlextra.partitioning import (
    PostgresPartitioningManager,
    PostgresCurrentTimePartitioningStrategy,
    PostgresTimePartitionSize,
    partition_by_current_time,
)
from psqlextra.partitioning.config import PostgresPartitioningConfig

manager = PostgresPartitioningManager([
    # 3 partitions ahead, each partition is one month
    # delete partitions older than 6 months
    # partitions will be named `[table_name]_[year]_[3-letter month name]`.
    PostgresPartitioningConfig(
        model=MyPartitionedModel,
        strategy=PostgresCurrentTimePartitioningStrategy(
            size=PostgresTimePartitionSize(months=1),
            count=3,
            max_age=relativedelta(months=6),
        ),
    ),
    # 6 partitions ahead, each partition is two weeks
    # delete partitions older than 8 months
    # partitions will be named `[table_name]_[year]_week_[week number]`.
    PostgresPartitioningConfig(
        model=MyPartitionedModel,
        strategy=PostgresCurrentTimePartitioningStrategy(
            size=PostgresTimePartitionSize(weeks=2),
            count=6,
            max_age=relativedelta(months=8),
        ),
    ),
    # 12 partitions ahead, each partition is 5 days
    # old partitions are never deleted, `max_age` is not set
    # partitions will be named `[table_name]_[year]_[month]_[month day number]`.
    PostgresPartitioningConfig(
        model=MyPartitionedModel,
        strategy=PostgresCurrentTimePartitioningStrategy(
            size=PostgresTimePartitionSize(days=5),
            count=12,
        ),
    ),

    # 24 partitions ahead, each partition is 1 hour, for a total of 24 hours. Starting with hour 0 of current day
    # old partitions are never deleted, `max_age` is not set
    # partitions will be named `[table_name]_[year]_[month]_[month day number]_[hour (24h)]:00:00`.
    PostgresPartitioningConfig(
        model=MyPartitionedModel,
        strategy=PostgresCurrentTimePartitioningStrategy(
            size=PostgresTimePartitionSize(hours=1),
            count=24,
        ),
    ),
])
```

--------------------------------

### Add to INSTALLED_APPS

Source: https://github.com/sectorlabs/django-postgres-extra/blob/master/docs/source/installation.rst

Include 'django.contrib.postgres' and 'psqlextra' in your Django project's INSTALLED_APPS setting. This enables the package's features.

```python
INSTALLED_APPS = [
    ...
    "django.contrib.postgres",
    "psqlextra",
]
```

--------------------------------

### Add List Partition using Schema Editor

Source: https://github.com/sectorlabs/django-postgres-extra/blob/master/docs/source/table_partitioning.rst

Use the schema editor's add_list_partition method to imperatively add a list partition.

```python
from django.db import connection

connection.schema_editor().add_list_partition(
    model=MyPartitionedModel,
    name="pt1",
    values=["car", "boat"],
)

```

--------------------------------

### Clone Repository

Source: https://github.com/sectorlabs/django-postgres-extra/blob/master/README.md

Clone the django-postgres-extra repository to your local machine.

```bash
λ git clone https://github.com/SectorLabs/django-postgres-extra.git
```

--------------------------------

### Create PostgreSQL User

Source: https://github.com/sectorlabs/django-postgres-extra/blob/master/README.md

Create a PostgreSQL superuser for use in tests. This is optional if your default user is already a superuser. It prompts for a password.

```bash
λ createuser --superuser psqlextra --pwprompt
λ export DATABASE_URL=postgres://psqlextra:<password>@localhost/psqlextra
```

--------------------------------

### Provide Custom Manager

Source: https://github.com/sectorlabs/django-postgres-extra/blob/master/docs/source/managers_models.rst

Provide PostgresManager as a custom manager with a specific name.

```python
from django.db import models
from psqlextra.manager import PostgresManager

class MyModel(models.Model):
    # custom mananger name
    beer = PostgresManager()

    myfield = models.CharField(max_length=255)

# use like this:
MyModel.beer.upsert(..)

# not like this:
MyModel.objects.upsert(..) # error!
```

--------------------------------

### Add Hash Partition using Schema Editor

Source: https://github.com/sectorlabs/django-postgres-extra/blob/master/docs/source/table_partitioning.rst

Use the schema editor's add_hash_partition method to imperatively add a hash partition.

```python
from django.db import connection

connection.schema_editor().add_hash_partition(
    model=MyPartitionedModel,
    name="pt1",
    modulus=3,
    remainder=1,
)

```

--------------------------------

### Add Range Partition using Schema Editor

Source: https://github.com/sectorlabs/django-postgres-extra/blob/master/docs/source/table_partitioning.rst

Use the schema editor's add_range_partition method to imperatively add a range partition.

```python
from django.db import connection

connection.schema_editor().add_range_partition(
    model=MyPartitionedModel,
    name="pt1",
    from_values="2019-01-01",
    to_values="2019-02-01",
)

```

--------------------------------

### Create a New Schema

Source: https://github.com/sectorlabs/django-postgres-extra/blob/master/docs/source/schemas.rst

Create a new PostgreSQL schema with a specified name using the `create` static method. This method will raise an error if the schema already exists.

```python
for psqlextra.schema import PostgresSchema

# will raise an error if the schema already exists
schema = PostgresSchema.create("myschema")
```

--------------------------------

### Re-create Schema if Necessary

Source: https://github.com/sectorlabs/django-postgres-extra/blob/master/docs/source/schemas.rst

Safely re-create a schema by dropping it first if it exists. Use `cascade=True` to drop all contents and references, but be cautious as this is a destructive operation.

```python
for psqlextra.schema import PostgresSchema
```

--------------------------------

### Add Hash Partition using Migration

Source: https://github.com/sectorlabs/django-postgres-extra/blob/master/docs/source/table_partitioning.rst

Use PostgresAddHashPartition to add a hash partition during Django migrations.

```python
from django.db import migrations, models

from psqlextra.backend.migrations.operations import PostgresAddHashPartition

class Migration(migrations.Migration):
    operations = [
        PostgresAddHashPartition(
           model_name="mypartitionedmodel",
           name="pt1",
           modulus=3,
           remainder=1,
        ),
    ]

```

--------------------------------

### Add Default Partition using Migration

Source: https://github.com/sectorlabs/django-postgres-extra/blob/master/docs/source/table_partitioning.rst

Use PostgresAddDefaultPartition to add a default partition during Django migrations. Only one default partition is allowed per table.

```python
from django.db import migrations, models

from psqlextra.backend.migrations.operations import PostgresAddDefaultPartition

class Migration(migrations.Migration):
    operations = [
        PostgresAddDefaultPartition(
           model_name="mypartitionedmodel",
           name="default",
        ),
    ]

```

--------------------------------

### Add Hash Partition

Source: https://github.com/sectorlabs/django-postgres-extra/blob/master/docs/source/table_partitioning.rst

Use this to add a hash partition to a model. Requires specifying the model, partition name, modulus, and remainder.

```python
connection.schema_editor().add_hash_partition(
    model=MyPartitionedModel,
    name="pt1",
    modulus=3,
    remainder=1,
)
```

--------------------------------

### Generate View Migration

Source: https://github.com/sectorlabs/django-postgres-extra/blob/master/docs/source/views.rst

Automatically generate a migration for creating a view or materialized view using the `pgmakemigrations` command. Always use this command for view models instead of the standard `makemigrations`.

```bash
python manage.py pgmakemigrations
```

--------------------------------

### Declare View with Raw SQL

Source: https://github.com/sectorlabs/django-postgres-extra/blob/master/docs/source/views.rst

Define a view or materialized view using raw SQL. For parameterized queries, provide a tuple with the SQL string and parameters.

```python
from django.db import models

from psqlextra.models import PostgresViewModel


class MyView(PostgresViewModel):
    name = models.TextField()
    somefk__name = models.TextField()

    class Meta:
        indexes = [models.Index(fields=["name"])]

    class ViewMeta:
        query = "SELECT id, somefk.name AS somefk__name FROM mytable INNER JOIN somefk ON somefk.id = mytable.somefk_id"

class MyMaterializedView(PostgresMaterializedViewModel):
    name = models.TextField()
    somefk__name = models.TextField()

    class Meta:
        indexes = [models.Index(fields=["name"])]

    class ViewMeta:
        query = ("SELECT id, somefk.name AS somefk__name FROM mytable INNER JOIN somefk ON somefk.id = mytable.somefk_id WHERE id > %s", 1)
```

--------------------------------

### Conflict Handling with Multiple Columns

Source: https://github.com/sectorlabs/django-postgres-extra/blob/master/docs/source/conflict_handling.rst

Demonstrates specifying multiple columns for conflict handling, typically used with unique_together constraints. Requires a unique constraint spanning 'first_name' and 'last_name'.

```python
from django.db import models
from psqlextra.models import PostgresModel

class MyModel(PostgresModel)
    class Meta:
        unique_together = ('first_name', 'last_name',)

    first_name = models.CharField(max_length=255)
    last_name = models.CharField(max_length=255)

obj = (
    MyModel.objects
    .on_conflict(['first_name', 'last_name'], ConflictAction.UPDATE)
    .insert_and_get(first_name='Henk', last_name='Jansen')
)
```

--------------------------------

### Configure Database Engine

Source: https://github.com/sectorlabs/django-postgres-extra/blob/master/docs/source/installation.rst

Set the 'ENGINE' in your Django DATABASES setting to 'psqlextra.backend'. This directs Django to use the custom PostgreSQL backend.

```python
DATABASES = {
    "default": {
        ...
        "ENGINE": "psqlextra.backend",
    },
}
```

--------------------------------

### Declare a Partitioned Model

Source: https://github.com/sectorlabs/django-postgres-extra/blob/master/docs/source/table_partitioning.rst

Inherit from `PostgresPartitionedModel` and define `PartitioningMeta` to specify the partitioning method and key.

```python
from django.db import models

from psqlextra.types import PostgresPartitioningMethod
from psqlextra.models import PostgresPartitionedModel

class MyModel(PostgresPartitionedModel):
    class PartitioningMeta:
        method = PostgresPartitioningMethod.RANGE
        key = ["timestamp"]

    name = models.TextField()
    timestamp = models.DateTimeField()
```

--------------------------------

### Drop and Create PostgreSQL Schema

Source: https://github.com/sectorlabs/django-postgres-extra/blob/master/docs/source/schemas.rst

Use PostgresSchema.drop_and_create to remove an existing schema and then create a new one. The cascade option can be used to drop dependent objects.

```python
from psqlextra.schema import PostgresSchema

# will drop existing schema named `myschema` if it
# exists and re-create it
schema = PostgresSchema.drop_and_create("myschema")

# will drop the schema and cascade it to its contents
# and anything referencing the schema
schema = PostgresSchema.drop_and_create("otherschema", cascade=True)
```

--------------------------------

### Reference an Existing Schema

Source: https://github.com/sectorlabs/django-postgres-extra/blob/master/docs/source/schemas.rst

Instantiate a PostgresSchema object to reference an existing schema and execute SQL queries within its context. Ensure the schema and its tables exist before execution.

```python
for psqlextra.schema import PostgresSchema

schema = PostgresSchema("myschema")

with schema.connection.cursor() as cursor:
    cursor.execute("SELECT * FROM tablethatexistsinmyschema")
```

--------------------------------

### Use PostgresManager On The Fly

Source: https://github.com/sectorlabs/django-postgres-extra/blob/master/docs/source/managers_models.rst

Use the postgres_manager utility to access psqlextra features within a context, useful for ManyToManyField through tables.

```python
from django.db import models
from psqlextra.util import postgres_manager

class MyModel(models.Model):
    myself = models.ManyToManyField('self')

# within the context, you can access psqlextra features
with postgres_manager(MyModel.myself.through) as manager:
    manager.upsert(...)
```

--------------------------------

### Conflict Handling with HStoreField Keys

Source: https://github.com/sectorlabs/django-postgres-extra/blob/master/docs/source/conflict_handling.rst

Demonstrates handling conflicts on keys within an HStoreField that has a uniqueness constraint. Requires an HStoreField with a 'uniqueness' defined for the 'en' key.

```python
from django.db import models
from psqlextra.models import PostgresModel
from psqlextra.fields import HStoreField

class MyModel(PostgresModel)
    name = HStoreField(uniqueness=['en'])

id = (
    MyModel.objects
    .on_conflict([('name', 'en')], ConflictAction.NOTHING)
    .insert(name={'en': 'Swen'})
)
```

--------------------------------

### Delete Default Partition using Migration

Source: https://github.com/sectorlabs/django-postgres-extra/blob/master/docs/source/table_partitioning.rst

Use PostgresDeleteDefaultPartition to remove an existing default partition. Be cautious as this can lead to insertion failures if no other partitions exist.

```python
from django.db import migrations, models

from psqlextra.backend.migrations.operations import PostgresDeleteDefaultPartition

class Migration(migrations.Migration):
    operations = [
        PostgresDeleteDefaultPartition(
           model_name="mypartitionedmodel",
           name="pt1",
        ),
    ]

```

--------------------------------

### Insert or Do Nothing

Source: https://github.com/sectorlabs/django-postgres-extra/blob/master/docs/source/conflict_handling.rst

Use `on_conflict` with `ConflictAction.NOTHING` to insert a new record if it does not exist, otherwise do nothing. This is efficient when the data to be inserted is identical to existing data. Note that only newly created rows are returned.

```python
# obj1 is _not_ none
obj1 = MyModel.objects.on_conflict(['name'], ConflictAction.NOTHING).insert(name="me")

# obj2 is none! object alreaddy exists
obj2 = MyModel.objects.on_conflict(['name'], ConflictAction.NOTHING).insert(name="me")
```

--------------------------------

### Format Code

Source: https://github.com/sectorlabs/django-postgres-extra/blob/master/README.md

Automatically format code, sort imports, and fix linting errors using the 'poe fix' command.

```bash
λ poe fix
```

--------------------------------

### Inherit from PostgresModel

Source: https://github.com/sectorlabs/django-postgres-extra/blob/master/docs/source/managers_models.rst

Inherit your model from PostgresModel to automatically use the psqlextra manager.

```python
from psqlextra.models import PostgresModel

class MyModel(PostgresModel):
    myfield = models.CharField(max_length=255)
```

--------------------------------

### Add List Partition using Migration Operation

Source: https://github.com/sectorlabs/django-postgres-extra/blob/master/docs/source/table_partitioning.rst

Use PostgresAddListPartition to add a new list partition to a model configured for LIST partitioning. Specify the model name, partition name, and the list of values the partition will hold.

```python
from django.db import migrations, models

from psqlextra.backend.migrations.operations import PostgresAddListPartition

class Migration(migrations.Migration):
    operations = [
        PostgresAddListPartition(
           model_name="mypartitionedmodel",
           name="pt1",
           values=["car", "boat"],
        ),
    ]
```

--------------------------------

### Add Default Partition

Source: https://github.com/sectorlabs/django-postgres-extra/blob/master/docs/source/table_partitioning.rst

Adds a default partition to a model. This is useful for handling rows that do not match any other partition criteria.

```python
from django.db import connection

connection.schema_editor().add_default_partition(
    model=MyPartitionedModel,
    name="default",
)
```

--------------------------------

### Create Random Name PostgreSQL Schema

Source: https://github.com/sectorlabs/django-postgres-extra/blob/master/docs/source/schemas.rst

Create a PostgreSQL schema with a name that includes a random suffix. The length of the suffix depends on system entropy.

```python
from psqlextra.schema import PostgresSchema

# schema name will be "myprefix_<8 random characters>"
schema = PostgresSchema.create_random("myprefix")
print(schema.name)
```

--------------------------------

### Declare View with Callable

Source: https://github.com/sectorlabs/django-postgres-extra/blob/master/docs/source/views.rst

Define a view or materialized view using a callable that returns raw SQL, parameterized SQL, or a queryset. This is useful when the query depends on runtime variables like settings.

```python
from django.db import models

from psqlextra.models import PostgresViewModel

def _generate_query():
    return ("SELECT * FROM sometable WHERE app_name = %s", settings.APP_NAME)

def _build_query():
    return SomeTable.objects.filter(app_name=settings.APP_NAME)


class MyView(PostgresViewModel):
    name = models.TextField()
    somefk__name = models.TextField()

    class ViewMeta:
        query = _generate_query

class MyMaterializedView(PostgresMaterializedViewModel):
    name = models.TextField()
    somefk__name = models.TextField()

    class ViewMeta:
        query = _generate_query
```

--------------------------------

### Conflict Handling with Specific Constraint Name

Source: https://github.com/sectorlabs/django-postgres-extra/blob/master/docs/source/conflict_handling.rst

Shows how to target a specific named constraint for conflict handling, useful when multiple unique constraints exist. Requires a pre-defined UniqueConstraint named 'myconstraint'.

```python
from django.db import models
from psqlextra.models import PostgresModel

class MyModel(PostgresModel)
    class Meta:
        constraints = [
            models.UniqueConstraint(
                name="myconstraint",
                fields=["first_name", "last_name"]
            ),
        ]

    first_name = models.CharField(max_length=255)
    last_name = models.CharField(max_length=255)

constraint = next(
    constraint
    for constraint in MyModel._meta.constraints
    if constraint.name == "myconstraint"
 ), None)

obj = (
    MyModel.objects
    .on_conflict(constraint, ConflictAction.UPDATE)
    .insert_and_get(first_name='Henk', last_name='Jansen')
)
```

--------------------------------

### Select an individual HStore key

Source: https://github.com/sectorlabs/django-postgres-extra/blob/master/docs/source/expressions.rst

Use HStoreRef to select a specific key from an hstore field. Ensure the model and field are correctly defined.

```python
from psqlextra.models import PostgresModel
from psqlextra.fields import HStoreField
from psqlextra.expressions import HStoreRef

class MyModel(PostgresModel):
   bla = HStoreField()

MyModel.objects.create(bla={'a': '1', 'b': '2'})

# '1'
a = (
   MyModel.objects
   .annotate(a=HStoreRef('bla', 'a'))
   .values_list('a', flat=True)
   .first()
)
```

--------------------------------

### Create Materialized View Without Data

Source: https://github.com/sectorlabs/django-postgres-extra/blob/master/docs/source/views.rst

To create a materialized view without initial data, use `PostgresCreateMaterializedViewModel` with `with_data=False`. Note that the first refresh cannot be `CONCURRENTLY` due to PostgreSQL restrictions, and the view is not queryable until refreshed.

```python
from django.db import migrations, models

from psqlextra.backend.migrations.operations import PostgresCreateMaterializedViewModel

class Migration(migrations.Migration):
    operations = [
        PostgresCreateMaterializedViewModel(
             name="myview",
             fields=[...],
             options={...},
             view_options={
                 "query": ...
             },
             # Not the default, creates materialized with `WITH NO DATA`
             with_data=False,
        )
    ]
```

--------------------------------

### Insert or Ignore with Unique Together Constraints

Source: https://github.com/sectorlabs/django-postgres-extra/blob/master/docs/source/conflict_handling.rst

Use `on_conflict` with `ConflictAction.NOTHING` to insert a new record or do nothing if a conflict arises based on unique together constraints defined in the model.

```python
class MyModel(PostgresModel)
    name = HStoreField(uniqueness=[('en', 'ar')])

id = (
    MyModel.objects
    .on_conflict([('name', 'en'), ('name', 'ar')], ConflictAction.NOTHING)
    .insert(name={'en': 'Swen'})
)
```

--------------------------------

### Standard Django Create Ignores on_conflict

Source: https://github.com/sectorlabs/django-postgres-extra/blob/master/docs/source/conflict_handling.rst

Illustrates that standard Django .create() method does not utilize the .on_conflict() clause. Use .insert() or .insert_and_get() for conflict handling.

```python
obj = (
    MyModel.objects
    .on_conflict(['first_name', 'last_name'], ConflictAction.UPDATE)
    .create(first_name='Henk', last_name='Jansen')
)

The same applies to methods such as :meth:`~django:django.db.models.query.QuerySet.update`, :meth:`~django:django.db.models.query.QuerySet.get_or_create` or :meth:`~django:django.db.models.query.QuerySet.update_or_create` etc.
```

--------------------------------

### Declare View with Queryset

Source: https://github.com/sectorlabs/django-postgres-extra/blob/master/docs/source/views.rst

Define a view or materialized view using a Django queryset. Ensure all fields returned by the query are declared as Django fields in the model.

```python
from django.db import models

from psqlextra.models import PostgresViewModel


class MyView(PostgresViewModel):
    name = models.TextField()
    somefk__name = models.TextField()

    class Meta:
        indexes = [models.Index(fields=["name"])]

    class ViewMeta:
        query = SomeOtherModel.objects.values('id', 'name', 'somefk__name')

class MyMaterializedView(PostgresMaterializedViewModel):
    name = models.TextField()
    somefk__name = models.TextField()

    class Meta:
        indexes = [models.Index(fields=["name"])]

    class ViewMeta:
        query = SomeOtherModel.objects.values('id', 'name', 'somefk__name')
```

--------------------------------

### Create Time-Based PostgreSQL Schema

Source: https://github.com/sectorlabs/django-postgres-extra/blob/master/docs/source/schemas.rst

Create a PostgreSQL schema with a name that includes a timestamp suffix. This is useful for creating unique schemas for specific operations.

```python
from psqlextra.schema import PostgresSchema

# schema name will be "myprefix_<timestamp>"
schema = PostgresSchema.create_time_based("myprefix")
print(schema.name)
```

--------------------------------

### Truncate

Source: https://github.com/sectorlabs/django-postgres-extra/blob/master/docs/source/deletion.rst

The `truncate` method on the `PostgresManager` allows for efficient deletion of all records in a table using the `TRUNCATE TABLE` SQL statement.

```APIDOC
## Truncate

### Description
Deletes all records in a table efficiently using `TRUNCATE TABLE`.

### Method
`PostgresManager.truncate()`

### Parameters
This method does not take any parameters in its basic form.

### Request Example
```python
from django.db import models
from psqlextra.models import PostgresModel

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

MyModel.objects.create(myfield="1")

MyModel.objects.truncate() # table is empty after this
print(MyModel.objects.count()) # zero records left
```

### Response
#### Success Response
This method does not return a value, but the table will be empty.

#### Response Example
```
0
```
```

--------------------------------

### Delete List Partition using Migration

Source: https://github.com/sectorlabs/django-postgres-extra/blob/master/docs/source/table_partitioning.rst

Use PostgresDeleteListPartition to delete a list partition. This operation is only applicable when the partitioned model uses LIST partitioning.

```python
from django.db import migrations, models

from psqlextra.backend.migrations.operations import PostgresDeleteListPartition

class Migration(migrations.Migration):
    operations = [
        PostgresDeleteListPartition(
           model_name="mypartitionedmodel",
           name="pt1",
        ),
    ]

```

--------------------------------

### Using Q Objects for Update Conditions

Source: https://github.com/sectorlabs/django-postgres-extra/blob/master/docs/source/conflict_handling.rst

With Django 3.1+, `Q` objects can be used to define update conditions in `on_conflict` operations, providing a more expressive way to specify complex criteria.

```python
from django.db.models import Q
from psqlextra.expressions import ExcludedCol

Q(name=ExcludedCol('name'))
Q(name__isnull=True)
Q(name__gt=ExcludedCol('priority'))
```

--------------------------------

### Bulk Upsert Rows (Shorthand)

Source: https://github.com/sectorlabs/django-postgres-extra/blob/master/docs/source/conflict_handling.rst

Use the shorthand bulk_upsert method for inserting or updating multiple rows efficiently. This is an older method equivalent to ConflictAction.UPDATE.

```python
(
    MyModel.objects
    .bulk_upsert(
        conflict_target=['myfield']
        rows=[
            dict(myfield='beer'),
            dict(myfield='wine')
        ]
    )
)
```

--------------------------------

### Check if a Schema Exists

Source: https://github.com/sectorlabs/django-postgres-extra/blob/master/docs/source/schemas.rst

Use the static `exists` method to check if a PostgreSQL schema with the given name is present in the database. This is useful for conditional operations.

```python
for psqlextra.schema import PostgresSchema

schema = PostgresSchema("myschema")
if PostgresSchema.exists("myschema"):
    print("exists!")
else:
    print('does not exist!')
```

--------------------------------

### Add Hash Partition using Migration Operation

Source: https://github.com/sectorlabs/django-postgres-extra/blob/master/docs/source/table_partitioning.rst

Use PostgresAddHashPartition to add a new hash partition to a model configured for HASH partitioning. This operation is used for hash partitioning.

```python
from django.db import migrations, models

from psqlextra.backend.migrations.operations import PostgresAddHashPartition

class Migration(migrations.Migration):
    operations = [
        PostgresAddHashPartition(
           model_name="mypartitionedmodel",
           name="pt1",
        ),
    ]
```

--------------------------------

### Custom Composite Primary Key

Source: https://github.com/sectorlabs/django-postgres-extra/blob/master/docs/source/table_partitioning.rst

Define a custom composite primary key that includes partitioning keys. This overrides the default behavior of including an auto-incrementing 'id' field.

```python
from django.db import models

from psqlextra.types import PostgresPartitioningMethod
from psqlextra.models import PostgresPartitionedModel

class MyModel(PostgresPartitionedModel):
    class PartitioningMeta:
        method = PostgresPartitioningMethod.RANGE
        key = ["timestamp"]

    # WARNING: This overrides default primary key that includes a auto-increment `id` field.
    pk = models.CompositePrimaryKey("name", "timestamp")

    name = models.TextField()
    timestamp = models.DateTimeField()
```

--------------------------------

### Temporary PostgreSQL Schema Context Manager

Source: https://github.com/sectorlabs/django-postgres-extra/blob/master/docs/source/schemas.rst

Use the postgres_temporary_schema context manager to create a schema that exists only within the 'with' block. The schema is automatically cleaned up upon exiting the block.

```python
from psqlextra.schema import postgres_temporary_schema

with postgres_temporary_schema("myprefix") as schema:
    pass

with postgres_temporary_schema("otherprefix", drop_on_throw=True) as schema:
    raise ValueError("drop it like it's hot")

with postgres_temporary_schema("greatprefix", cascade=True) as schema:
    with schema.connection.cursor() as cursor:
        cursor.execute(f"CREATE TABLE {schema.name} AS SELECT 'hello'")

with postgres_temporary_schema("amazingprefix", drop_on_throw=True, cascade=True) as schema:
    with schema.connection.cursor() as cursor:
        cursor.execute(f"CREATE TABLE {schema.name} AS SELECT 'hello'")

    raise ValueError("oops")
```

--------------------------------

### Delete Range Partition using Migration

Source: https://github.com/sectorlabs/django-postgres-extra/blob/master/docs/source/table_partitioning.rst

Use PostgresDeleteRangePartition to delete a range partition. This operation is only applicable when the partitioned model uses RANGE partitioning.

```python
from django.db import migrations, models

from psqlextra.backend.migrations.operations import PostgresDeleteRangePartition

class Migration(migrations.Migration):
    operations = [
        PostgresDeleteRangePartition(
           model_name="mypartitionedmodel",
           name="pt1",
        ),
    ]

```

--------------------------------

### Delete Hash Partition using Migration

Source: https://github.com/sectorlabs/django-postgres-extra/blob/master/docs/source/table_partitioning.rst

Use PostgresDeleteHashPartition to delete a hash partition. This operation is only applicable when the partitioned model uses HASH partitioning.

```python
from django.db import migrations, models

from psqlextra.backend.migrations.operations import PostgresDeleteHashPartition

class Migration(migrations.Migration):
    operations = [
        PostgresDeleteHashPartition(
           model_name="mypartitionedmodel",
           name="pt1",
        ),
    ]

```

--------------------------------

### Multi-field coalesce with IsNotNone

Source: https://github.com/sectorlabs/django-postgres-extra/blob/master/docs/source/expressions.rst

Use IsNotNone for a coalesce-like operation across multiple fields, selecting the first non-null value. A default value can be provided.

```python
from psqlextra.models import PostgresModel
from psqlextra.fields import HStoreField
from psqlextra.expressions import IsNotNone

class MyModel(PostgresModel):
   name_1 = models.TextField(null=True)
   name_2 = models.TextField(null=True)
   name_3 = models.TextField(null=True)

MyModel.objects.create(name_3='test')

# 'test'
name = (
   MyModel.objects
   .annotate(name=IsNotNone('name_1', 'name_2', 'name_3', default='buh'))
   .values_list('name', flat=True)
   .first()
)

# 'buh'
name = (
   MyModel.objects
   .annotate(name=IsNotNone('name_1', 'name_2', default='buh'))
   .values_list('name', flat=True)
   .first()
)
```

--------------------------------

### Delete Partition

Source: https://github.com/sectorlabs/django-postgres-extra/blob/master/docs/source/table_partitioning.rst

Removes a specified partition from a model. Ensure the partition name is correct before deletion.

```python
from django.db import connection

connection.schema_editor().delete_partition(
    model=MyPartitionedModel,
    name="default",
)
```

--------------------------------

### Bulk Insert with Conflict Update

Source: https://github.com/sectorlabs/django-postgres-extra/blob/master/docs/source/conflict_handling.rst

Use bulk_insert with on_conflict to update existing rows or insert new ones based on a unique constraint. This method performs a single query for efficiency.

```python
from django.db import models
from psqlextra.models import PostgresModel

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

obj = (
    MyModel.objects
    .on_conflict(['name'], ConflictAction.UPDATE)
    .bulk_insert([
        dict(name='swen'),
        dict(name='henk'),
        dict(name='adela')
    ])
)
```

--------------------------------

### Custom Composite Primary Key with Auto-Incrementing ID

Source: https://github.com/sectorlabs/django-postgres-extra/blob/master/docs/source/table_partitioning.rst

Define a custom composite primary key that explicitly includes an auto-incrementing 'id' field along with partitioning keys.

```python
from django.db import models

from psqlextra.types import PostgresPartitioningMethod
from psqlextra.models import PostgresPartitionedModel

class MyModel(PostgresPartitionedModel):
    class PartitioningMeta:
        method = PostgresPartitioningMethod.RANGE
        key = ["timestamp"]

    id = models.AutoField(primary_key=True)
    pk = models.CompositePrimaryKey("id", "timestamp")

    name = models.TextField()
    timestamp = models.DateTimeField()
```

--------------------------------

### Truncate with Cascade

Source: https://github.com/sectorlabs/django-postgres-extra/blob/master/docs/source/deletion.rst

The `truncate` method can be used with the `cascade=True` option to delete related rows in other tables, preventing errors due to foreign key constraints.

```APIDOC
## Truncate with Cascade

### Description
Truncates a table and cascades the operation to related tables by setting `cascade=True`.

### Method
`PostgresManager.truncate(cascade=True)`

### Parameters
*   **cascade** (bool) - Optional - If `True`, the truncate operation will cascade to related tables.

### Request Example
```python
from django.db import models
from psqlextra.models import PostgresModel

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


class MyModel2(PostgresModel):
    mymodel1 = models.ForeignKey(MyModel1, on_delete=models.CASCADE)

obj1 = MyModel1.objects.create(myfield="1")
MyModel2.objects.create(mymodel1=obj1)

MyModel1.objects.truncate(cascade=True)
print(MyModel1.objects.count()) # zero records left
print(MyModel2.objects.count()) # zero records left
```

### Response
#### Success Response
This method does not return a value, but the table and related tables will be empty.

#### Response Example
```
0
0
```
```

--------------------------------

### Truncate Table

Source: https://github.com/sectorlabs/django-postgres-extra/blob/master/docs/source/deletion.rst

Use the truncate method to quickly delete all records from a table. This is significantly faster than standard Django deletion methods.

```python
from django.db import models
from psqlextra.models import PostgresModel

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

MyModel.objects.create(myfield="1")

MyModel.objects.truncate() # table is empty after this
print(MyModel.objects.count()) # zero records left
```

--------------------------------

### Upsert Single Row (Shorthand)

Source: https://github.com/sectorlabs/django-postgres-extra/blob/master/docs/source/conflict_handling.rst

Use the shorthand upsert method for inserting or updating a single row without returning the primary key. This is an older method equivalent to ConflictAction.UPDATE.

```python
id = (
    MyModel.objects
    .upsert(
        conflict_target=['myfield']
        fields=dict(myfield='beer')
    )
)
```

--------------------------------

### Override Default Manager

Source: https://github.com/sectorlabs/django-postgres-extra/blob/master/docs/source/managers_models.rst

Override the default Django manager with PostgresManager in your model.

```python
from django.db import models
from psqlextra.manager import PostgresManager

class MyModel(models.Model):
    # override default django manager
    objects = PostgresManager()

    myfield = models.CharField(max_length=255)
```

--------------------------------

### Refresh Materialized View

Source: https://github.com/sectorlabs/django-postgres-extra/blob/master/docs/source/views.rst

Use `MyViewModel.refresh()` to refresh a materialized view with an AccessExclusive lock. For concurrent reads, use `MyViewModel.refresh(concurrently=True)`, which requires the view to have been refreshed at least once previously.

```python
# Takes an AccessExclusive lock and blocks till table is re-filled
MyViewModel.refresh()

# Allows concurrent read, does block till table is re-filled.
# Warning: Only works if the view was refreshed at least once before.
MyViewModel.refresh(concurrently=True)
```

--------------------------------

### Upsert with Update Condition

Source: https://github.com/sectorlabs/django-postgres-extra/blob/master/docs/source/conflict_handling.rst

Perform an upsert operation using `ConflictAction.UPDATE`. An optional condition can be specified to only apply the update if the condition evaluates to true. A row-level lock is acquired before evaluating the condition.

```python
from psqlextra.expressions import CombinedExpression, ExcludedCol

pk = (
    MyModel
    .objects
    .on_conflict(
        ['name'],
        ConflictAction.UPDATE,
        update_condition=CombinedExpression(
            MyModel._meta.get_field('priority').get_col(MyModel._meta.db_table),
            '>',
            ExcludedCol('priority'),
        )
    )
    .insert(
        name='henk',
        priority=1,
    )
)

if pk:
    print('update applied or inserted')
else:
    print('condition was false-ish and no changes were made')
```

--------------------------------

### Create a Case-Insensitive Unique Index

Source: https://github.com/sectorlabs/django-postgres-extra/blob/master/docs/source/indexes.rst

CaseInsensitiveUniqueIndex creates a unique index that ignores the casing of the specified field(s). This makes the field behave similarly to a case-insensitive text field in other database systems. It prevents entries that differ only in case from being considered duplicates.

```python
from django.db import models
from psqlextra.indexes import CaseInsensitiveUniqueIndex

class Model(models.Model):
    class Meta:
        indexes = [
            CaseInsensitiveUniqueIndex(fields=['name'])
        ]

    name = models.CharField(max_length=255)

Model.objects.create(name='henk')
Model.objects.create(name='Henk') # raises IntegrityError

```

--------------------------------

### Truncate Table with Cascade

Source: https://github.com/sectorlabs/django-postgres-extra/blob/master/docs/source/deletion.rst

Enable cascade to delete related rows in other tables when truncating a table. This prevents errors caused by foreign key constraints.

```python
from django.db import models
from psqlextra.models import PostgresModel

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


class MyModel2(PostgresModel):
    mymodel1 = models.ForeignKey(Model1, on_delete=models.CASCADE)

obj1 = MyModel1.objects.create(myfield="1")
MyModel2.objects.create(mymodel1=obj1)

MyModel.objects.truncate(cascade=True)
print(MyModel1.objects.count()) # zero records left
print(MyModel2.objects.count()) # zero records left
```

--------------------------------

### Locking an Arbitrary Table with EXCLUSIVE Lock

Source: https://github.com/sectorlabs/django-postgres-extra/blob/master/docs/source/locking.rst

Acquire an exclusive lock on an arbitrary table, optionally specifying a schema, within a durable transaction. Locks are automatically released when the transaction commits.

```python
from django.db import transaction

from psqlextra.locking import PostgresTableLockMode, postgres_lock_table

with transaction.atomic(durable=True):
    postgres_lock_table("mytable", PostgresTableLockMode.EXCLUSIVE)
    postgres_lock_table(
        "tableinotherschema",
        PostgresTableLockMode.EXCLUSIVE,
        schema_name="myschema"
    )
```

--------------------------------

### Create a Unique Index with UniqueIndex

Source: https://github.com/sectorlabs/django-postgres-extra/blob/master/docs/source/indexes.rst

Use UniqueIndex to create a unique index on model fields. This is particularly useful for views and materialized views where Django's default `unique=True` on fields does not apply. It prevents duplicate entries based on the specified fields.

```python
from django.db import models
from psqlextra.indexes import UniqueIndex

class Model(models.Model):
    class Meta:
        indexes = [
            UniqueIndex(fields=['name'])
        ]

    name = models.CharField(max_length=255)

Model.objects.create(name='henk')
Model.objects.create(name='henk') # raises IntegrityError

```

--------------------------------

### Drop PostgreSQL Schema

Source: https://github.com/sectorlabs/django-postgres-extra/blob/master/docs/source/schemas.rst

Use PostgresSchema.drop to remove a PostgreSQL schema. The cascade option can be used to drop dependent objects. The 'public' schema cannot be dropped.

```python
from psqlextra.schema import PostgresSchema

schema = PostgresSchema.drop("myprefix")
schema = PostgresSchema.drop("myprefix", cascade=True)
```

--------------------------------

### HStoreField Combined Uniqueness Constraint

Source: https://github.com/sectorlabs/django-postgres-extra/blob/master/docs/source/hstore.rst

Specify combined uniqueness constraints for multiple keys in an HStoreField. This enforces that the combination of values for specified keys must be unique. Note: For Django 2.2+, consider using UniqueConstraint.

```python
myfield = HStoreField(uniqueness=[('key1', 'key2'), 'key3'])
```