1. Introduction

Django by default, has a large collection of possible types that can be used to define the model. But sometimes we need to use some more complex types offered by PostgreSQL. In this case, we will look the integrating of PostgreSQL array with Django.

Warning

This package is renamed from djorm-ext-pgarray to djorm-pgarray.

Api and import statements are not changed. Only python package index toplevel package name is changed.

2. Requirements

  • Python 2.7, 3.3 or 3.4

  • Django 1.5, 1.6 and 1.7

  • Psycopg2 >= 2.5.0

3. Features

  • Shortcut fields for almost all default django field types.

  • Django >=1.7 custom lookups for easy querying.

  • User defined types support out of the box.

  • Multiple dimensions arrays.

4. User guide

4.1. Installation

The simplest way to install djorm-pgarray is using pip:

pip install djorm-pgarray

4.2. Configure

Nothing to configure, use field as is. How you are using any other django builtin model field.

4.3. Quick start

This is a sample definition of model using a ArrayField:

from django.db import models
from djorm_pgarray.fields import IntegerArrayField

class Register(models.Model):
    name = models.CharField(max_length=200)
    points = IntegerArrayField()

Now, you can use this model and store python native lists on points field:

>>> Register.objects.create(points=[1,2,3,4])
<Register: Register object>
>>> obj = Register.objects.get()
>>> obj.points
[1,2,3,4]

4.4. Arrays with multiple dimensions

PostgreSQL also has support for arrays with multiple dimensions, and you can use them with djorm-pgarray:

Sample model definition
class Filter(models.Model):
    name = models.CharField(max_length=200)
    tag_groups = TextArrayField(dimension=2) # this creates text[][] postgresql field.

Now you can store on Filter.tag_groups field arrays with two dimensions:

>>> tag_groups = [["Hello", "World"], ["Hola", "Mundo"]]
>>> Filter.objects.create(tag_groups=tag_groups)
<Filter: Filter object>

4.5. Not defaultly supported types

djorm-pgarray comes with limited set of supported types, but it can be extended for use with different types. In this case we go to use bytea arrays.

Simple model definition
class ModelWithBytes(models.Model):
    entries = ArrayField(dbtype="bytea")

Now, you can save byte data and retrieve it

>>> data = [memoryview(b"\x01\x00\x00\x00\x00\x00"),
...         memoryview(b"\x02\x00\x00\x00\x00\x00"),
...         memoryview(b"\x03\x00\x00\x00\x00\x00")]
>>> ModelWithBytes.objects.create(entries=data)
>>> obj = ModelWithBytes.objects.get()
>>> assert obj.entries[0].tobytes() == data[0].tobytes()

4.6. Querying

Custom lookups for easy querying array fields are only available for django >= 1.7. For other django versions, queryset.extra() should be used for these purpose.

4.6.1. contains

The contains lookup is overridden on ArrayField. The returned objects will be those where the values passed are a subset of the data. It uses @> operator:

>>> Page.objects.create(name="First page", tags=["foo", "bar"])
>>> Page.objects.create(name="Second page", tags=["foo"])
>>> Page.objects.filter(tags__contains=["foo"])
[<Page: First page>, <Page: Second page>]

4.6.2. contained_by

This is the inverse of the contains lookup - the objects returned will be those where the data is a subset of the values passed. It uses <@ operator:

>>> Page.objects.create(name="First page", tags=["foo", "bar"])
>>> Page.objects.create(name="Second page", tags=["foo"])
>>> Page.objects.filter(tags__contained_by=["foo", "bar"])
[<Page: First page>, <Page: Second page>]

4.6.3. overlap

Returns objects where the data shares any results with the values passed. It uses the && operator:

>>> Page.objects.create(name="First page", tags=["foo", "bar"])
>>> Page.objects.create(name="Second page", tags=["foo"])
>>> Page.objects.filter(tags__overlap=["foo"])
[<Page: First page>, <Page: Second page>]

4.6.4. len

Returns the length of the array

>>> Page.objects.create(name="First page", tags=["foo", "bar"])
>>> Page.objects.create(name="Second page", tags=["foo"])
>>> Page.objects.filter(tags__len=1)
[<Page: Second page>]

4.6.5. index & slice

Allow search by array index:

>>> Page.objects.create(name="First page", tags=["foo", "bar"])
>>> Page.objects.create(name="Second page", tags=["foo"])
>>> Page.objects.filter(tags__0="foo")
[<Page: First page>, <Page: Second page>]

And allow you to take a slice of the array:

>>> Page.objects.create(name="First page", tags=["foo", "bar"])
>>> Page.objects.create(name="Second page", tags=["foo"])
>>> Page.objects.filter(tags__0_1=["foo"])
[<Page: First page>]

5. Api Reference

This is a list of available fields:

  • djorm_pgarray.fields.ArrayField (Generic field)

  • djorm_pgarray.fields.SmallIntegerArrayField

  • djorm_pgarray.fields.IntegerArrayField

  • djorm_pgarray.fields.BigIntegerArrayField

  • djorm_pgarray.fields.FloatArrayField

  • djorm_pgarray.fields.TextArrayField

  • djorm_pgarray.fields.DateArrayField

  • djorm_pgarray.fields.DateTimeArrayField

5.1. ArrayField

Is a generic/base field which inherited by the rest, and accept the following parameters:

  • dbtype: string that represents the database type

  • dimension: integer that represents the array dimension

  • type_cast: function that represents the type cast function.

The rest of ArrayField subclasses are simple aliases with corresponding dbtype value.

6. License

Copyright (c) 2013-2014 Andrey Antukh <niwi@niwi.be>

All rights reserved.

Redistribution and use in source and binary forms, with or without
modification, are permitted provided that the following conditions
are met:
1. Redistributions of source code must retain the above copyright
   notice, this list of conditions and the following disclaimer.
2. Redistributions in binary form must reproduce the above copyright
   notice, this list of conditions and the following disclaimer in the
   documentation and/or other materials provided with the distribution.
3. The name of the author may not be used to endorse or promote products
   derived from this software without specific prior written permission.

THIS SOFTWARE IS PROVIDED BY THE AUTHOR ``AS IS'' AND ANY EXPRESS OR
IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES
OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE DISCLAIMED.
IN NO EVENT SHALL THE AUTHOR BE LIABLE FOR ANY DIRECT, INDIRECT,
INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT
NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE,
DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY
THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
(INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF
THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.