Skip to main content

PostgreSQL Specific Features

This project provides specialized fields and search utilities that leverage PostgreSQL's advanced data types. These features allow for more complex data structures and efficient querying directly within the Django ORM.

PostgreSQL Specific Fields​

The codebase implements several fields in django.contrib.postgres.fields that map directly to PostgreSQL-specific types like ARRAY, HSTORE, and various RANGE types.

ArrayField: Storing Ordered Lists​

The ArrayField, defined in django/contrib/postgres/fields/array.py, allows you to store lists of data. It requires a base_field, which is another Django field instance that defines the type of the items in the array.

Definition and Basic Usage​

You can define an ArrayField with any non-relational field as its base.

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

class IntegerArrayModel(models.Model):
    # A list of integers
    field = ArrayField(models.IntegerField(), default=list, blank=True)
    
    # Nested arrays are also supported
    nested_field = ArrayField(ArrayField(models.IntegerField()))

Querying and Indexing​

The ArrayField implementation provides several lookups and transforms:

  • Contains and Overlap: Use __contains to find arrays containing specific elements and __overlap for arrays sharing any elements.
  • Length: Use __len to filter by the number of items in the array.
  • Indexing: Django uses 0-based indexing for array access, which the ArrayField.get_transform method automatically converts to PostgreSQL's 1-based indexing.
# Find records where the first element is 1
IntegerArrayModel.objects.filter(field__0=1)

# Find records where the array contains both 1 and 2
IntegerArrayModel.objects.filter(field__contains=[1, 2])

# Filter by array length
IntegerArrayModel.objects.filter(field__len__gt=2)

HStoreField: Key-Value Storage​

The HStoreField in django/contrib/postgres/fields/hstore.py stores sets of key-value pairs where both keys and values are strings (or null values).

Definition and Key Transforms​

When querying an HStoreField, you can access specific keys directly using transforms.

from django.contrib.postgres.fields import HStoreField

class HStoreModel(models.Model):
    field = HStoreField(blank=True, null=True)

# Querying by a specific key 'foo'
HStoreModel.objects.filter(field__foo='bar')

# Checking for the existence of a key
HStoreModel.objects.filter(field__has_key='baz')

The get_prep_value method ensures that all keys and non-null values are cast to strings before being sent to the database, maintaining compatibility with the underlying hstore type.

RangeFields: Working with Intervals​

The project includes several range fields in django/contrib/postgres/fields/ranges.py, such as IntegerRangeField, BigIntegerRangeField, DecimalRangeField, DateTimeRangeField, and DateRangeField.

Range Boundaries and Containment​

Range fields support complex interval logic, including checking if a value falls within a range or if two ranges overlap.

from django.contrib.postgres.fields import IntegerRangeField, DateTimeRangeField

class RangesModel(models.Model):
    ints = IntegerRangeField(blank=True, null=True)
    timestamps = DateTimeRangeField(default_bounds='[]')

# Check if an integer is within the range
RangesModel.objects.filter(ints__contains=5)

# Check if two ranges overlap
RangesModel.objects.filter(ints__overlap=(1, 10))

Continuous ranges (like DateTimeRangeField) inherit from ContinuousRangeField, which allows specifying default_bounds (e.g., [) for inclusive lower and exclusive upper bounds).

Full Text Search​

The django.contrib.postgres.search module provides high-level abstractions for PostgreSQL's full-text search capabilities, primarily through SearchVector and SearchQuery.

SearchVector and SearchQuery​

SearchVector represents the document(s) being searched, while SearchQuery represents the search terms.

  • SearchVector: Can combine multiple fields using the + operator. It uses the to_tsvector PostgreSQL function.
  • SearchQuery: Supports different search types via the search_type parameter: plain (default), phrase, raw, and websearch.
from django.contrib.postgres.search import SearchVector, SearchQuery

# Basic search across multiple fields
results = Entry.objects.annotate(
    search=SearchVector('body_text', 'blog__tagline')
).filter(search='django')

# Phrase search
query = SearchQuery('brave sir robin', search_type='phrase')
results = Line.objects.annotate(search=SearchVector('dialogue')).filter(search=query)

Weighting and Ranking​

You can assign weights ('A', 'B', 'C', or 'D') to different parts of a SearchVector to influence search results. The SearchRank function then calculates how well a document matches the query.

from django.contrib.postgres.search import SearchRank

vector = SearchVector('title', weight='A') + SearchVector('body', weight='B')
query = SearchQuery('cheese')

# Order results by relevance
Entry.objects.annotate(rank=SearchRank(vector, query)).order_by('-rank')

The SearchVector.as_sql implementation handles the setweight PostgreSQL function when a weight is provided, ensuring that the specified importance is correctly applied during the search.