By default, Django applications are configured to store data into a lightweight SQLite database file. While this works well under some loads, a more traditional DBMS can improve performance in production. In this guide, we’ll demonstrate how to install and configure PostgreSQL to use with your Django applications. PostgreSQL allows the definition of custom range types. Django’s model and form field implementations use base classes below, and psycopg2 provides a registerrange to allow use of custom range types. Class RangeField(.options)¶ Base class for model range fields. Basefield¶ The model field class to use. Rangetype¶ The psycopg2 range.

Helpful Resources

Installing PostgreSQL

• Installing PostgreSQL on Mac, Windows, Ubuntu, and Docker

Connecting to PostgreSQL

• Toad Edge, Netbeans, and Eclipse
• Django, Laravel, and PHP

How-tos for Developers and DBA’s

Oracle to PostgreSQL migration resources

Moving from Oracle to PostgreSQL and other data migration resources

Oracle to PostgreSQL Migration Guide

PostgreSQL Extensions

• PgPool-II for connection pooling, PgBouncer for high availability, PostGIS for geo-spatial data

Free online training, a wide range of tutorials, PostgreSQL comparisons and resources based on Developers, DBAs and DevOps on over 15 years of supporting the world’s most demanding PostgreSQL implementations. Developers working with PostgreSQL can find resources related to Query optimization, Node.js, Java, Python, and other programming languages. DBAs can find PostgreSQL resources related to tuning, performance optimization, configuration, high availability, replication and cloud.

These functions are available from the django.contrib.postgres.aggregatesmodule. They are described in more detail in the PostgreSQL docs.

Note

All functions come without default aliases, so you must explicitly provideone. For example:

Common aggregate options

All aggregates have the filter keywordargument.

General-purpose aggregation functions¶

ArrayAgg¶

class ArrayAgg(expression, distinct=False, filter=None, ordering=(), **extra)¶

Returns a list of values, including nulls, concatenated into an array.

distinct¶

An optional boolean argument that determines if array valueswill be distinct. Defaults to False.

ordering¶

An optional string of a field name (with an optional '-' prefixwhich indicates descending order) or an expression (or a tuple or listof strings and/or expressions) that specifies the ordering of theelements in the result list.

Examples:

BitAnd¶

class BitAnd(expression, filter=None, **extra)¶

Returns an int of the bitwise AND of all non-null input values, orNone if all values are null.

BitOr¶

class BitOr(expression, filter=None, **extra)¶

Django Postgresql Array

Returns an int of the bitwise OR of all non-null input values, orNone if all values are null.

BoolAnd¶

class BoolAnd(expression, filter=None, **extra)¶

Returns True, if all input values are true, None if all values arenull or if there are no values, otherwise False .

Usage example:

BoolOr¶

class BoolOr(expression, filter=None, **extra)¶

Returns True if at least one input value is true, None if allvalues are null or if there are no values, otherwise False.

Usage example:

JSONBAgg¶

class JSONBAgg(expressions, distinct=False, filter=None, ordering=(), **extra)¶

Returns the input values as a JSON array.

distinct¶

An optional boolean argument that determines if array values will bedistinct. Defaults to False.

ordering¶

An optional string of a field name (with an optional '-' prefixwhich indicates descending order) or an expression (or a tuple or listof strings and/or expressions) that specifies the ordering of theelements in the result list.

Django Postgresql Windows

Examples are the same as for ArrayAgg.ordering.

StringAgg¶

class StringAgg(expression, delimiter, distinct=False, filter=None, ordering=())¶

Returns the input values concatenated into a string, separated bythe delimiter string.

delimiter¶

Required argument. Needs to be a string.

Python Django Postgresql

distinct¶

An optional boolean argument that determines if concatenated valueswill be distinct. Defaults to False.

ordering¶

An optional string of a field name (with an optional '-' prefixwhich indicates descending order) or an expression (or a tuple or listof strings and/or expressions) that specifies the ordering of theelements in the result string.

Examples are the same as for ArrayAgg.ordering.

Aggregate functions for statistics¶

y and x¶

The arguments y and x for all these functions can be the name of afield or an expression returning a numeric data. Both are required.

Corr¶

class Corr(y, x, filter=None)¶

Returns the correlation coefficient as a float, or None if therearen’t any matching rows.

CovarPop¶

class CovarPop(y, x, sample=False, filter=None)¶

Returns the population covariance as a float, or None if therearen’t any matching rows.

Has one optional argument:

sample¶

By default CovarPop returns the general population covariance.However, if sample=True, the return value will be the samplepopulation covariance.

RegrAvgX¶

class RegrAvgX(y, x, filter=None)¶

Returns the average of the independent variable (sum(x)/N) as afloat, or None if there aren’t any matching rows.

RegrAvgY¶

class RegrAvgY(y, x, filter=None)¶

Returns the average of the dependent variable (sum(y)/N) as afloat, or None if there aren’t any matching rows.

RegrCount¶

class RegrCount(y, x, filter=None)¶

Returns an int of the number of input rows in which both expressionsare not null.

RegrIntercept¶

class RegrIntercept(y, x, filter=None)¶

Returns the y-intercept of the least-squares-fit linear equation determinedby the (x,y) pairs as a float, or None if there aren’t anymatching rows.

RegrR2¶

class RegrR2(y, x, filter=None)¶

Returns the square of the correlation coefficient as a float, orNone if there aren’t any matching rows.

RegrSlope¶

class RegrSlope(y, x, filter=None)¶

Django Postgresql Ssl

Returns the slope of the least-squares-fit linear equation determinedby the (x,y) pairs as a float, or None if there aren’t anymatching rows.

RegrSXX¶

class RegrSXX(y, x, filter=None)¶

Returns sum(x^2)-sum(x)^2/N (“sum of squares” of the independentvariable) as a float, or None if there aren’t any matching rows.

RegrSXY¶

class RegrSXY(y, x, filter=None)¶

Django Postgresql Configuration

Returns sum(x*y)-sum(x)*sum(y)/N (“sum of products” of independenttimes dependent variable) as a float, or None if there aren’t anymatching rows.

RegrSYY¶

class RegrSYY(y, x, filter=None)¶

Returns sum(y^2)-sum(y)^2/N (“sum of squares” of the dependentvariable) as a float, or None if there aren’t any matching rows.

Django Postgresql Ssl

Usage examples¶

We will use this example table:

Here’s some examples of some of the general-purpose aggregation functions:

The next example shows the usage of statistical aggregate functions. Theunderlying math will be not described (you can read about this, for example, atwikipedia):