Why use postgres

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