Support GENERATED ALWAYS columns for MySQL and PostgreSQL

[Louise Grandjonc]

Hi,

in Postgres 12, generated columns have been added: ​https://www.postgresql.org/docs/12/ddl-generated-columns.html

The value of a generated column is computed from other columns. A generated column can be either stored (in which case it takes up space, and is written/updated like any other column) or virtual.

Here is the example in the postgres docs to create such columns:

CREATE TABLE people (
    ...,
    height_cm numeric,
    height_in numeric GENERATED ALWAYS AS (height_cm / 2.54) STORED
);

I would love to contribute and add this feature. And I'd like to know a bit what other think in terms of design:
Maybe the expression could be a string:

class People(models.Model):
    height_cm = models.FloatField()
    height_in = models.FloatField(always_generated='height_cm / 2.54', stored=True)

Best,
Louise

[Nick Pope]

This would be nice. I'd imagine tweaking your proposed API to look a bit more like the following:

class Persistence(enum.Enum):
    STORED = 'STORED'
    VIRTUAL = 'VIRTUAL'

class People(models.Model):
    height_cm = models.FloatField()
    height_in = models.FloatField(generated=F('height_cm') / 2.54, persistence=Persistence.STORED)

• always_generated seems a bit long and it is always GENERATED ALWAYS AS.
• It would be nice to build expressions using the ORM with F(), etc.
• Specifying the "persistence" (is that the best name) keyword with an enum would allow reuse of that elsewhere.

There are a number of complex issues that need to be addressed:

• The new value needs to be returned on INSERT or UPDATE. (Support for INSERT via RETURNING was added in Django 3.0)
• Any column with generated needs to be excluded from INSERT or UPDATE queries.
• We'd need to use a feature flag to specify which "persistence" keywords are supported, i.e. PostgreSQL only supports STORED.

[Mariusz Felisiak]

Duplicate of #31300.