.. index::
   ! Functional indexes

.. _django_3_2_functional_indexes:

================================================================
**Django 3.2 LTS** Functional indexes
================================================================

.. seealso::

   - https://docs.djangoproject.com/en/dev/releases/3.2/#functional-indexes
   - https://github.com/django/django/commit/83fcfc9ec8610540948815e127101f1206562ead
   - https://twitter.com/hannseman
   - https://twitter.com/m_holtermann
   - https://twitter.com/pauloxnet/status/1347977856371089408?s=20
   - https://twitter.com/be_haki/status/1348294571218919426?s=20
   - https://twitter.com/pauloxnet/status/1348298649906896896?s=20

.. contents::
   :depth: 3


Pull request Added support for functional indexes. #11929
=============================================================


.. seealso::

   - https://github.com/django/django/pull/11929
   - https://github.com/django/django/commit/83fcfc9ec8610540948815e127101f1206562ead

This is enables pretty powerful expressions like having tsvector GIN-indexes
on regular columns::

    GinIndex(
        fields=[Cast('field', SearchVectorField())], name=index_name
    )

or B-Tree indexes on keys in jsonb columns which values are not text::

    Index(
        fields=[Cast(KeyTextTransform('some_key', 'field'), IntegerField())], name=index_name
    )


Twitter Announces
==================


pauloxnet
--------------

.. figure:: annonces_twitter.png
   :align: center

   https://twitter.com/pauloxnet/status/1347977856371089408?s=20

::

    Very Nice! Always exciting to see such features being added to the ORM

    You are right. In the last project I would have needed the functional
    indexes and looking in the Django code I realized that there was
    already an open PR.

    It is nice to be part of a community where many collaborate for growth.

    Full-text search and spatial data are the Django functions I deal with most.

    Think of all the functions this can support!

Adam Johnson
------------

.. figure:: annonces_twitter_2.png

   https://twitter.com/AdamChainz/status/1348295366924500993?s=20


::

    Think of all the functions this can support!


::

    @hannseman  has already included two very interesting examples in the
    PR description:

    - tsvector GIN indexes on regular columns
    - B-Tree indexes on keys in JSONB columns which values are not text
    Very useful


Description
=================

The new \*expressions positional argument of Index() enables creating
functional indexes on expressions and database functions.

For example

.. code-block:: python
   :linenos:

    from django.db import models
    from django.db.models import F, Index, Value
    from django.db.models.functions import Lower, Upper


    class MyModel(models.Model):
        first_name = models.CharField(max_length=255)
        last_name = models.CharField(max_length=255)
        height = models.IntegerField()
        weight = models.IntegerField()

        class Meta:
            indexes = [
                Index(
                    Lower('first_name'),
                    Upper('last_name').desc(),
                    name='first_last_name_idx',
                ),
                Index(
                    F('height') / (F('weight') + Value(5)),
                    name='calc_idx',
                ),
            ]

Functional indexes are added to models using the **Meta.indexes** option