From 83fcfc9ec8610540948815e127101f1206562ead Mon Sep 17 00:00:00 2001 From: Hannes Ljungberg Date: Thu, 10 Oct 2019 20:04:17 +0200 Subject: Fixed #26167 -- Added support for functional indexes. Thanks Simon Charette, Mads Jensen, and Mariusz Felisiak for reviews. Co-authored-by: Markus Holtermann --- docs/ref/models/indexes.txt | 51 ++++++++++++++++++++++++++++++++++++++++++--- 1 file changed, 48 insertions(+), 3 deletions(-) (limited to 'docs/ref/models') diff --git a/docs/ref/models/indexes.txt b/docs/ref/models/indexes.txt index 9dda45ad44..d2cf98e9e1 100644 --- a/docs/ref/models/indexes.txt +++ b/docs/ref/models/indexes.txt @@ -21,10 +21,55 @@ options`_. ``Index`` options ================= -.. class:: Index(fields=(), name=None, db_tablespace=None, opclasses=(), condition=None, include=None) +.. class:: Index(*expressions, fields=(), name=None, db_tablespace=None, opclasses=(), condition=None, include=None) Creates an index (B-Tree) in the database. +``expressions`` +--------------- + +.. attribute:: Index.expressions + +.. versionadded:: 3.2 + +Positional argument ``*expressions`` allows creating functional indexes on +expressions and database functions. + +For example:: + + Index(Lower('title').desc(), 'pub_date', name='lower_title_date_idx') + +creates an index on the lowercased value of the ``title`` field in descending +order and the ``pub_date`` field in the default ascending order. + +Another example:: + + Index(F('height') * F('weight'), Round('weight'), name='calc_idx') + +creates an index on the result of multiplying fields ``height`` and ``weight`` +and the ``weight`` rounded to the nearest integer. + +:attr:`Index.name` is required when using ``*expressions``. + +.. admonition:: Restrictions on Oracle + + Oracle requires functions referenced in an index to be marked as + ``DETERMINISTIC``. Django doesn't validate this but Oracle will error. This + means that functions such as + :class:`Random() ` aren't accepted. + +.. admonition:: Restrictions on PostgreSQL + + PostgreSQL requires functions and operators referenced in an index to be + marked as ``IMMUTABLE``. Django doesn't validate this but PostgreSQL will + error. This means that functions such as + :class:`Concat() ` aren't accepted. + +.. admonition:: MySQL and MariaDB + + Functional indexes are ignored with MySQL < 8.0.13 and MariaDB as neither + supports them. + ``fields`` ---------- @@ -130,8 +175,8 @@ indexes records with more than 400 pages. .. admonition:: Oracle Oracle does not support partial indexes. Instead, partial indexes can be - emulated using functional indexes. Use a :doc:`migration - ` to add the index using :class:`.RunSQL`. + emulated by using functional indexes together with + :class:`~django.db.models.expressions.Case` expressions. .. admonition:: MySQL and MariaDB -- cgit v1.3