Introduction #

django-postgres-extra is a 3rd party Django package that can help to use all of PostgreSQL's features available through the Django ORM. Developer will focus only for coding instead of writing complex SQL queries. Many small Django packages try to implement a single feature to Django with minimal effort and simplicity. But, django-postgres-extra made more efforts to get many useful features implemented with well tested implementations, seamless migrations and much more.

With seamless we mean that any features that can be added will work truly seamlessly. Developer should not have to manually modify migrations to work with fields and objects provided by this package.

How to install #

1. Install package from PyPi

   $ pip install django-postgres-extra
   

2. Add postgres_extra and django.contrib.postgres to INSTALLED_APPS:

   INSTALLED_APPS = [
   ...
   'django.contrib.postgres',
   'psqlextra'
   ]
   

3. Set the database engine to psqlextra.backend:

   DATABASES = {
       ...
       'default': {
           'ENGINE': 'psqlextra.backend'
       }
   }
   

4. Make sure all models that inherit from psqlextra.models.PostgresModel or use the psqlextra.manager.PostgresManager. Without this, most features do not work.

Features #

1. Native upserts
   • Single query
   • Concurrency safe
   • With bulk support (single query)
2. Extended support for HStoreField
   • Unique constraints
   • Null constraints
   • Select individual keys using .values() or .values_list()
3. Extra signals
   • Updates
4. Extra expressions
   • MIN and MAX for multiple value fields such as hstore and array
5. Indexes
   • Custom indexes with conditions

Examples #

Manager #

Using the manager #

django-postgres-extra has psqlextra.manager.PostgresManager model manager which has a lot of functionality. The models must use this manager in order to use most of this package's functionality.

There are four ways to do this:

1. psqlextra.models.PostgresModel inherit model:

   from psqlextra.models import PostgresModel
   
   class MyIncoModel(PostgresModel):
       name = models.CharField(max_length=255)
   

2. Use psqlextra.manager.PostgresManager custom manager:

   from django.db import models
   from psqlextra.manager import PostgresManager
   
   class MyBookStore(models.Model):
       # custom mananger name
       books = PostgresManager()
   
       name = models.CharField(max_length=255)
   
   # use like this:
   MyBookStore.books.upsert(..)
   
   # not like this:
   MyBookStore.objects.upsert(..) # error!
   

3. Use psqlextra.util.postgres_manager as context manager:

   from django.db import models
   from psqlextra.util import postgres_manager
   
   class MyBookStore(models.Model):
       name = models.ManyToManyField('self')
   
   # within the context, you can access psqlextra features
   with postgres_manager(MyBookStore.myself.through) as manager:
       manager.upsert(...)
   

Conflict handling #

django-postgres-extra also provides PostgreSQL's ON CONFLICT DO ... feature, allowing blazing fast and concurrency safe inserts:

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

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

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

# insert, or do nothing if it already exists, then fetch
obj1 = (
    MyBookStore.objects
    .on_conflict(['name'], ConflictAction.NOTHING)
    .insert_and_get(name='Inco Books')
)

# insert or update if already exists, then fetch only the primary key
id = (
    MyBookStore.objects
    .on_conflict(['name'], ConflictAction.UPDATE)
    .insert(name='Inco Books')
)

HStore #

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

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

class Book(PostgresModel):
    data = HStoreField(uniqueness=['en'])

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

We can also specify a list of strings to specify the keys that must be marked as unique:

from psqlextra.fields import HStoreField
from psqlextra.models import PostgresModel

class Book(PostgresModel):
    data = HStoreField(uniqueness=['key1']

Book.objects.create(myfield={'key1': 'value1'})
Book.objects.create(myfield={'key1': 'value1'})

Signals #

psqlextra.signals.create #

Send after a new model instance was created:

from django.db import models
from psqlextra.models import PostgresModel
from psqlextra import signals

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

def on_create(sender, **kwargs):
     print('model created with pk %d' % kwargs['pk'])

signals.create.connect(Book, on_create, weak=False)

# this will trigger the signal
instance = Book(name='War and Peace')
instance.save()

# but so will this
Book.objects.create(name='Harry Potter')

psqlextra.signals.update #

Send after a new model instance was updated:

from django.db import models
from psqlextra.models import PostgresModel
from psqlextra import signals

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

def on_update(sender, **kwargs):
     print('model updated with pk %d' % kwargs['pk'])

signals.update.connect(Book, on_update, weak=False)

# for every row that is affected, the signal will be send
Book.objects.filter(name='War and Peace').update(amount=21)

psqlextra.signals.delete #

Send before a new model instance is deleted:

from django.db import models
from psqlextra.models import PostgresModel
from psqlextra import signals

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

def on_delete(sender, **kwargs):
     print('model deleted with pk %d' % kwargs['pk'])

signals.delete.connect(Book, on_update, weak=False)

# for every row that is affected, the signal will be send
Book.objects.filter(name='War and Peace').delete()

# in this case, a single row is deleted, the signal will be send
# for this particular row
Book.objects.get(id=1).delete()

Indexes #

Conditional Unique Index

The ConditionalUniqueIndex lets us to create partial unique indexes in case we need unique together constraints on nullable columns.

Before:

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)

After:

from django.db import models
from 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)

References #

1. http://django-postgres-extra.readthedocs.io/

Suggested Pages #

• 0.025 JDK
• 0.025 PyDev
• 0.025 Jython
• 0.025 자이썬
• 0.025 인터프리터
• 0.025 이클립스
• 0.025 HTML
• 0.025 MVT
• 0.025 Eclipse
• 0.025 MVC
• More suggestions...