#36792: Add support for virtual generated columns (PostgreSQL 18+) in
GeneratedField
-------------------------------------+-------------------------------------
Reporter: Paolo Melchiorre | Type: New
| feature
Status: new | Component: Database
| layer (models, ORM)
Version: dev | Severity: Normal
Keywords: postgresql | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
=== Background ===
PostgreSQL 18 introduces virtual generated columns: columns declared with
GENERATED ALWAYS AS ( <generation_expr> ) VIRTUAL are computed at query
time
and do not occupy storage. PostgreSQL 18 also makes VIRTUAL the default
for
generated columns while continuing to support STORED (materialized)
generated
columns. See the PostgreSQL 18 release notes:
[https://www.postgresql.org/about/news/postgresql-18-released-3142/
PostgreSQL 18 release notes].
=== Motivation ===
Django's GeneratedField support currently targets databases that support
STORED
(materialized) generated columns. With PostgreSQL 18, users should be able
to
declare GeneratedFields that are VIRTUAL (computed at query time) and have
Django generate the correct DDL and perform correct introspection and
migration autogeneration.
=== Goals ===
* Add support in Django's PostgreSQL backend to emit the VIRTUAL keyword
in
CREATE TABLE and ALTER TABLE statements for generated columns when
targeting
PostgreSQL 18 or newer.
* Extend PostgreSQL introspection to detect whether an existing generated
column is STORED or VIRTUAL and populate migration state accordingly so
autogeneration can produce correct AddField / AlterField operations.
* Ensure migration operations handle switching between STORED and VIRTUAL
where supported by the server, and emit clear errors or warnings when
not
supported by the connected server version.
* Add tests and documentation; skip/guard tests on PostgreSQL servers
older
than 18.
=== Implementation notes and suggestions ===
* Server gating: use the existing {{{server_version_int}}} checks (treat
PostgreSQL 18 as the cutoff, e.g. {{{180000}}}) so that DDL including
VIRTUAL is only emitted on servers that support it.
* DDL generation: update the PostgreSQL schema editor (schema
creation/alter) to emit either "VIRTUAL" or "STORED" based on field
metadata and server version. If the field model exposes a {{{stored}}}
boolean, emit {{{STORED}}} when True and {{{VIRTUAL}}} when False (and
server >= 18).
* Introspection: extend postgresql introspection to read the generation
expression and the storage type (stored vs virtual) from
{{{information_schema}}} and/or {{{pg_catalog}}} and expose those details
in the field_info consumed by the migration autogenerator.
* Migrations: ensure {{{AlterField}}} and {{{AddField}}} include the
storage attribute and that migration autogeneration treats changes in
stored/virtual as schema changes. If the connected server does not support
VIRTUAL, raise a clear error during migration planning/execution.
* Tests: add backend tests that:
* create a model with a GeneratedField stored=True (STORED) and
stored=False (VIRTUAL),
* introspect an existing table with VIRTUAL columns,
* autogenerate migrations for switching between STORED and VIRTUAL,
* ensure tests are skipped on PostgreSQL < 18.
* Documentation: update GeneratedField docs to describe the stored vs
virtual option and PostgreSQL version requirements (PG 18+ for VIRTUAL).
Recommend keeping DB versions consistent across environments.
=== Examples ===
SQL (PG18+):
{{{
CREATE TABLE example (
a integer,
b integer GENERATED ALWAYS AS (a + 1) VIRTUAL
);
}}}
Stored column:
{{{
CREATE TABLE example (
a integer,
b integer GENERATED ALWAYS AS (a + 1) STORED
);
}}}
Django (draft API):
{{{
class MyModel(models.Model):
width = models.IntegerField()
area = models.GeneratedField(models.F('width') * 2, stored=False) #
VIRTUAL on PG18+
}}}
=== Backward-compatibility and migration considerations ===
* Emit {{{VIRTUAL}}} only when connected to PG 18+; otherwise use
{{{STORED}}} or raise a clear error.
* Autogenerated migrations created on PG18 that include {{{VIRTUAL}}}
should be documented or guarded to avoid failures on older servers.
* Recommend teams keep DB versions consistent across dev/test/prod if they
plan to use {{{VIRTUAL}}} columns.
=== Related ===
* Similar work for Oracle persisted generated fields:
[https://code.djangoproject.com/ticket/36765 #36765]
* PostgreSQL 18 release notes:
[https://www.postgresql.org/about/news/postgresql-18-released-3142/]
--
Ticket URL: <https://code.djangoproject.com/ticket/36792>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
--
You received this message because you are subscribed to the Google Groups
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To view this discussion visit
https://groups.google.com/d/msgid/django-updates/0107019b0cb5cad9-5fad47d4-13b3-45b0-b751-73e8ec731f5b-000000%40eu-central-1.amazonses.com.
