Cannot update the generation expression for a generated column / make the limitation explicit

From: PG Doc comments form <noreply(at)postgresql(dot)org>
To: pgsql-docs(at)lists(dot)postgresql(dot)org
Cc: martinlbuchanan(at)gmail(dot)com
Subject: Cannot update the generation expression for a generated column / make the limitation explicit
Date: 2023-03-30 21:23:16
Message-ID: 168021139638.632.1465154267735726195@wrigleys.postgresql.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-docs

The following documentation comment has been logged on the website:

Page: https://www.postgresql.org/docs/15/sql-altertable.html
Description:

In the many forms of ALTER TABLE, described in
https://www.postgresql.org/docs/current/sql-altertable.html, it appears that
a generated column, defined with generated always as (expression) stored,
cannot have its generation expression updated.

There is a DROP EXPRESSION option that can remove the generation expression,
but no option to add back the generation expression with a different
definition.

My documentation recommendation is to be explicit about this limitation in
both the ALTER TABLE web page and the page that discusses generated columns,
https://www.postgresql.org/docs/15/ddl-generated-columns.html.

Dropping the column and recreating it (1) to preserve column order would
also require, I believe, dropping and recreating subsequent columns in the
same table; (2) often requires dropping and recreating objects that
reference the column unless there is a technique I have overlooked; (3)
which can, for the PG database I support, require dropping and recreating a
stack of dozens of SQL objects including materialized views that contain
much of our content.

So my related functionality recommendation is to add the option to ALTER
TABLE to assign the generation expression. (The term generated can also
involve identity columns or sequence references; I am not suggesting any
changes to that functionality.)

Browse pgsql-docs by date

  From Date Subject
Next Message Daniel Gustafsson 2023-03-31 08:25:24 Re: Minor typo in 13.3.5. Advisory Locks
Previous Message David G. Johnston 2023-03-29 21:14:21 Re: I think that the transaction tutorial document (3.4) should mention transaction isolation