Django Postgresql

 

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

PostgreSQL on Containers

PostgreSQL Tutorials

Courses and Training

How-tos for Developers and DBA’s

PostgreSQL Developer Resources

Programming Frameworks

Administration and DBA Resources

PostgreSQL Vs. Other Databases

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

Django Postgresql
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
New in Django 3.2.

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
Django Postgresql

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
Django postgresql ssl

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):