Warning

In order for any of the features described below to work, you must use the PostgresManager or inherit your models from PostgresModel. Read more about this in Managers & Models.

Conflict handling

The PostgresManager comes with full support for PostgreSQL’s ON CONFLICT clause.

This is an extremely useful feature for doing concurrency safe inserts. Often, when you want to insert a row, you want to overwrite it already exists, or simply leave the existing data there. This would require a SELECT first and then possibly a INSERT. Within those two queries, another process might make a change to the row.

The alternative of trying to insert, ignoring the error and then doing a UPDATE is also not good. That would result in a lot of write overhead (due to logging).

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 if already exists, then fetch only the primary key
id = (
    MyModel.objects
    .on_conflict(['myfield'], ConflictAction.UPDATE)
    .insert(myfield='beer')
)

Warning

The standard Django methods for inserting/updating are not affected by on_conflict(). It was a conscious decision to not override or change their behavior. The following completely ignores the on_conflict():

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 update(), get_or_create() or update_or_create() etc.

Constraint specification

The on_conflict() function’s first parameter denotes the name of the column(s) in which the conflict might occur. Although you can specify multiple columns, these columns must somehow have a single constraint. For example, in case of a unique_together constraint.

Multiple columns

Specifying multiple columns is necessary in case of a constraint that spans multiple columns, such as when using Django’s unique_together.

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')
)

HStore keys

Catching conflicts in columns with a UNIQUE constraint on a HStoreField key is also supported:

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'})
)

This also applies to “unique together” constraints in a HStoreField field:

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

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

insert vs insert_and_get

After specifying on_conflict() you can use either insert() or insert_and_get() to perform the insert.

Conflict actions

There’s currently two actions that can be taken when encountering a conflict. The second parameter of on_conflict() allows you to specify that should happen.

ConflictAction.UPDATE

psqlextra.types.ConflictAction.UPDATE

  • If the row does not exist, insert a new one.

  • If the row exists, update it.

This is also known as a “upsert”.

ConflictAction.NOTHING

psqlextra.types.ConflictAction.NOTHING

  • If the row does not exist, insert a new one.

  • If the row exists, do nothing.

This is preferable when the data you’re about to insert is the same as the one that already exists. This is more performant because it avoids a write in case the row already exists.

Warning

When using NOTHING, PostgreSQL only returns the row(s) that were created. Conflicting rows are not returned. See example below:

# 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")

This applies to both insert() and bulk_insert()

Bulk

bulk_insert() allows your to use conflict resolution for bulk inserts:

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')
    ])
)

bulk_insert() uses a single query to insert all specified rows at once. It returns a list of dict with each dict being a merge of the dict passed in along with any index returned from Postgres.

Note

In order to stick to the “everything in one query” principle, various, more advanced usages of bulk_insert() are impossible. It is not possible to have different rows specify different amounts of columns. The following example does not work:

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')
    ])
)

An exception is thrown if this behavior is detected.

Shorthands

The on_conflict(), insert() and insert_or_create() methods were only added in v1.6. Before that, only UPDATE was supported in the following form:

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')
    )
)

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

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

These two short hands still exist and are not deprecated. They behave exactly the same as UPDATE and are there for convenience. It is up to you to decide what to use.