HStoreField

• Simple key-value pairs
• Corresponds to Python dictionary
• All keys, values are strings

from django.db import models

from django.contrib.postgres.fields import HStoreField


class Customer(models.Model):
    name = models.CharField(max_length=254)
    email = models.EmailField()

    preferences = HStoreField()

...

Customer.objects.create(name='Razzi Abuissa',
                        email='razzi53@gmail.com',
                        preferences={'notification_emails': 'disable'})

>>> Customer.objects.filter(preferences__has_key='notification_emails')
SELECT "id", "name", "email", "preferences"
FROM "customers_customer"
WHERE "preferences" ? 'notification_emails';
args=('notification_emails',)

[<Customer: Customer object>]

>>> Customer.objects.filter(
        preferences__contains={'notification_texts': 'disabled'}
    )

SELECT "id", "name", "email", "preferences"
FROM "customers_customer"
WHERE "preferences" @> hstore(ARRAY['notification_texts'], ARRAY['disabled'])
args=({'notification_texts': 'disabled'},)

[]

class PostgresSimpleLookup(Lookup):
    def as_sql(self, qn, connection):
        lhs, lhs_params = self.process_lhs(qn, connection)
        rhs, rhs_params = self.process_rhs(qn, connection)
        params = lhs_params + rhs_params
        return '%s %s %s' % (lhs, self.operator, rhs), params


class DataContains(PostgresSimpleLookup):
    lookup_name = 'contains'
    operator = '@>'


class HasKey(PostgresSimpleLookup):
    lookup_name = 'has_key'
    operator = '?'


HStoreField.register_lookup(lookups.DataContains)
HStoreField.register_lookup(lookups.HasKey)

class Customer(models.Model):
    preferences = HStoreField()

...

class HasKey(PostgresSimpleLookup):
    lookup_name = 'has_key'
    operator = '?'


HStoreField.register_lookup(HasKey)

...

Customers.objects.filter(preferences__has_key='notification_emails')
-> query.add_q(Q(*args, **kwargs))

-> self.build_filter(('preferences__has_key', 'notification_emails'))
  -> self.solve_lookup_type('preferences__has_key')
# => lookups = ['has_key'], parts = ['preferences']

-> condition = self.build_lookup(lookups, col, value)
  -> col.get_lookup('has_key')
# => django.contrib.postgres.lookups.HasKey object

... WHERE "customers_customer"."preferences" ? 'notification_emails'

ArrayField

• Compound datatype: takes another field as argument
• Eliminates need for junction table

ArrayField Example

from django.db import models
from django.contrib.postgres.fields import ArrayField


class Ticket(models.Model):
    description = models.TextField()
    assigned_users = ArrayField(models.IntegerField)


Ticket.objects.filter(assigned_users__contains=[1])
Ticket.objects.filter(assigned_users__contained_by=[1,2])
Ticket.objects.filter(assigned_users__overlap=[2,4])
Ticket.objects.filter(assigned_users__len=0)

Another Example

from django.db import models
from django.contrib.postgres.fields import ArrayField


class Post(models.Model):
    name = models.CharField(max_length=200)
    tags = ArrayField(models.CharField(max_length=200))


Post.objects.create(name='First post', tags=['thoughts', 'django'])
Post.objects.create(name='Third post', tags=['tutorial', 'django'])

# ArrayFields are ordered!
Post.objects.filter(tags__0=['thoughts'])

Range Fields

• IntegerRangeField
• BigIntegerRangeField

• FloatRangeField

• DateTimeRangeField

• DateRangeField

Can also define custom range types

Range Fields

-- @>: Containment
... where holidays @> 3;

-- Vanilla SQL
... where start_day < 3
AND end_day > 3;

• higher performance
• more expressive

Range Fields

import datetime

from psycopg2.extras import DateRange

planned_vacation = DateRange(datetime.date(...), datetime.date(...))

Conferences.objects.filter(date__contains=planned_vacation)