Postgres and 

Why use postgres

Huge community

Huge number of features

Best support for geodjango

Partial indexes

Window queries

Full text search

Common table Expressions

Flexible datatypes

Permissive licence

Django 1.8 adds three concepts to the database

Arrays

Ranges

Unstructured data

Arrays

Writing

from django.db import models

class Brand(models.Model):
    name = models.CharField(max_length=40)

    def __str__(self):
        return self.name

class Park(models.Model):
    brands = models.ManyToManyField(Brand)

Old way

Writing

In [1]: global_studios = Park().objects.create()

In [2]: global_studios.brands.all()  
Out[2]: []

In [3]: wizards = Brand.objects.create(name="wizards wizards wizards")

In [4]: global_studios.brands.add(wizards)

In [5]: robots = Brand.objects.create(name="cars robots explosions")

In [6]: global_studios.brands.add(robots)

In [7]: [str(brand) for brand in global_studios.brands.all()]
Out[7]: ['wizards wizards wizards', 'cars robots explosions']

In [8]: x = Park.objects.get(id=global_studios.id)  

Old way

Array Field

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

class Parks(models.Model):
    brands = ArrayField(models.CharField(max_length=50,
                                         blank=True),
                        blank=True, default=list)

Writing

In [1]: global_studios = Park()

In [2]: global_studios.brands
Out[2]: []

In [3]: global_studios.brands.append("wizards wizards wizards")

In [4]: global_studios.brands.append("cars robots explosions")

In [5]: global_studios.brands
Out[5]: ['wizards wizards wizards', 'cars robots explosions']

In [6]: global_studios.save()

In [7]: Park.objects.get(brands__0__icontains="wizards")
In [8]: Park.objects.get(brands__len=2)
In [9]: x = Park.objects.get(brands__contains=['wizards wizards wizards'])

In [10]: x.brands
Out[10]: ['wizards wizards wizards', 'cars robots explosions']

Using it

WHY

  • Fewer database hits
  • Simpler data relationships
  • Easier querying
  • Nested data
  • Max size
  • Ordered data

Ranges

Writing

from django.db import models

class Park(models.Model):
    name = ...
    brands = ... 
    open_start = models.DateField()
    open_end = models.DateField()

Old way

Writing

In [1]: mouse_designated_area  = Park()

In [2]: mouse_designated_area.open_start = date(year=2015, month=3, day=1)

In [3]: mouse_designated_area.open_end = date(year=2015, month=11, day=3)

In [4]: mouse_designated_area.save()

In [5]: freedom_day = date(year=2015, month=7, day=4)

In [6]: Park.objects.filter(open_start__gt=freedom_day).filter(open_end__lt=freedom_day)

Old way

Range Field

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

class Park(models.Model):
    name = ...
    brands = ...
    open = DateRangeField(null=True)


using it

In [1]: mouse_designated_area = Park()

In [2]: mouse_designated_area.open = (date(year=2015, month=3, day=1), 
                               date(year=2015, month=11, day=3))
In [3]: mouse_designated_area.save()

In [4]: Park.objects.get(open__contains=date(year=2015, month=7, day=4))

Why

simpler 

 

Unstructured Data

from django.db import models

class Park(models.Model):
    name = ...
    brands = ... 
    open = ...
    park_details = TextField()

Old way

Hstore Field

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

class Park(models.Model):
    name = ...
    brands = ...
    open = ...
    marketing_plan = HStoreField()


Add postgres to installed packages

Add HStore extension to Migrations

using it

In [1]: yups_pear_orchard = Park()

In [2]: yups_pear_orchard.marketing_plan['mascot'] = 'dog'

In [3]: yups_pear_orchard.save()

In [4]: Park.objects.filter(marketing_plan__has_key='mascot')  

In [5]: p = Park.objects.filter(marketing_plan__contains={'mascot': 'dog'}) 

In [6]: p.marketing_plan
Out[6]: {'mascot': 'dog'}

 

Why

Real dictionary

Query by keys

Query by values

 

Json Field

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

class Park(models.Model):
    name = ...
    brands = ...
    open = ...
    marketing_plan = ...
    concessions = JSONField()

using it

In [1]: fish_planet = Park()  

In [2]: fish_planet.concessions = {"drinks": ['soda', 'juice'], 
                                   'foods': {"veggies":["carrots"], 
                                            "meat":['bacon']}}

In [3]: fish_planet.save()  

In [4]: Park.objects.filter(concessions__foods__meat=["bacon"])

Why

Real json

Great lookup syntax

indexable 

DRF Support

ListField

DictField

ArrayAgg

BitAnd

BitOr

BoolAnd

A list of values, concatenated into an array.

An int of the bitwise AND 

True, if all input values are true

An int of the bitwise OR.

BoolOr

True, if any input values are true

StringAgg

Input values concatenated into a string

Postgres  aggregation functions.

Thanks!

Ben@Lightmatter.com

Postgres and Django

By Ben Beecher

Postgres and Django

  • 985