Django

ArrayField - a PostgreSQL-specific field

Syntax#

  • from django.contrib.postgres.fields import ArrayField
  • class ArrayField(base_field, size=None, **options)
  • FooModel.objects.filter(array_field_name__contains=[objects, to, check])
  • FooModel.objects.filter(array_field_name__contained_by=[objects, to, check])

Remarks#

Note that although the size parameter is passed to PostgreSQL, PostgreSQL will not enforce it.

When using ArrayFields one should keep in mind this word of warning from the Postgresql arrays documentation.

Tip: Arrays are not sets; searching for specific array elements can be a sign of database misdesign. Consider using a separate table with a row for each item that would be an array element. This will be easier to search, and is likely to scale better for a large number of elements.

A basic ArrayField

To create a PostgreSQL ArrayField, we should give ArrayField the type of data we want it to store as a field as its first argument. Since we’ll be storing book ratings, we will use FloatField.

 from django.db import models, FloatField
 from django.contrib.postgres.fields import ArrayField
 
 class Book(models.Model):
     ratings = ArrayField(FloatField())

Specifying the maximum size of an ArrayField

 from django.db import models, IntegerField
 from django.contrib.postgres.fields import ArrayField
 
 class IceCream(models.Model):
     scoops = ArrayField(IntegerField()  # we'll use numbers to ID the scoops
                   , size=6)  # our parlor only lets you have 6 scoops

When you use the size parameter, it’s passed through to postgresql, which accepts it and then ignores it! Thus it’s quite possible to add 7 integers to the scoops field above using the postgresql console.

Querying for membership of ArrayField with contains

This query returns all cones with a chocolate scoop and a vanilla scoop.

VANILLA, CHOCOLATE, MINT, STRAWBERRY = 1, 2, 3, 4  # constants for flavors
choco_vanilla_cones = IceCream.objects.filter(scoops__contains=[CHOCOLATE, VANILLA])

Don’t forget to import the IceCream model from your models.py file.

Also bear in mind that django will not create an index for ArrayFields. If you are going to search them, you are going to need an index and it will need to be manually created with a call to RunSQL in your migrations file.

Nesting ArrayFields

You can nest ArrayFields by passing another ArrayField as it’s base_field.

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

class SudokuBoard(models.Model):
    numbers = ArrayField(
        ArrayField(
            models.IntegerField(),
            size=9,
        ),
        size=9,
    )

Querying for all models who contain any item in a list with contained_by

This query returns all cones with either a mint scoop or a vanilla scoop.

minty_vanilla_cones = IceCream.objects.filter(scoops__contained_by=[MINT, VANILLA])

This modified text is an extract of the original Stack Overflow Documentation created by the contributors and released under CC BY-SA 3.0 This website is not affiliated with Stack Overflow