Opened 2 years ago

Closed 2 years ago

#31565 closed New feature (duplicate)

Support GENERATED ALWAYS columns for MySQL and PostgreSQL

Reported by: Louise Grandjonc Owned by: nobody
Component: Database layer (models, ORM) Version: dev
Severity: Normal Keywords:
Cc: Triage Stage: Accepted
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description

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

Change History (2)

comment:1 Changed 2 years ago by Nick Pope

Triage Stage: UnreviewedAccepted

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.

comment:2 Changed 2 years ago by Mariusz Felisiak

Resolution: duplicate
Status: newclosed

Duplicate of #31300.

Note: See TracTickets for help on using tickets.
Back to Top