From: PG Doc comments form <noreply@postgresql.org>
To: pgsql-docs@lists.postgresql.org
Cc: martinlbuchanan@gmail.com
Subject: Cannot update the generation expression for a generated column / make the limitation explicit
Date: Thu, 30 Mar 2023 21:23:16 +0000
Message-ID: <168021139638.632.1465154267735726195@wrigleys.postgresql.org> (raw)
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.)
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-docs@postgresql.org
Cc: noreply@postgresql.org, pgsql-docs@lists.postgresql.org, martinlbuchanan@gmail.com
Subject: Re: Cannot update the generation expression for a generated column / make the limitation explicit
In-Reply-To: <168021139638.632.1465154267735726195@wrigleys.postgresql.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox