Note that ALTER TABLE cannot alter generated stored columns

public inbox for [email protected]  
help / color / mirror / Atom feed

From: Emre Hasegeli <[email protected]>
To: [email protected]
Subject: Note that ALTER TABLE cannot alter generated stored columns
Date: Sun, 30 May 2021 16:25:23 +0300
Message-ID: <CAE2gYzw0dUtL4PZ6_zfwyF9yCpejuRMWh_9gFgf3s8YEqxCN0Q@mail.gmail.com> (raw)

I was trying to change the generation expression of a generated stored
column using ALTER TABLE.  It took me a while before I figured SET
GENERATED applies only to generated identity columns.  I think it'd be
useful to note this in the documentation with something like the
attached.


Attachments:

  [application/octet-stream] 0001-doc-Note-that-ALTER-TABLE-cannot-alter-generated-sto.patch (2.0K, 2-0001-doc-Note-that-ALTER-TABLE-cannot-alter-generated-sto.patch)
  download | inline diff:
From 3d6fe7e6bb57081a9b7c3d5bcc23e5159ed65ba7 Mon Sep 17 00:00:00 2001
From: Emre Hasegeli <[email protected]>
Date: Sun, 30 May 2021 16:11:30 +0300
Subject: [PATCH] doc: Note that ALTER TABLE cannot alter generated stored

ALTER TABLE supports altering generated identity columns which could
confuse the reader as they share syntax with generated stored columns.
Clarify that a column cannot be turned into generated stored or
generation expressions cannot be changed with a note.
---
 doc/src/sgml/ref/alter_table.sgml | 8 ++++++++
 1 file changed, 8 insertions(+)

diff --git a/doc/src/sgml/ref/alter_table.sgml b/doc/src/sgml/ref/alter_table.sgml
index 939d3fe273..0e9846153e 100644
--- a/doc/src/sgml/ref/alter_table.sgml
+++ b/doc/src/sgml/ref/alter_table.sgml
@@ -251,20 +251,28 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
       This form turns a stored generated column into a normal base column.
       Existing data in the columns is retained, but future changes will no
       longer apply the generation expression.
      </para>
 
      <para>
       If <literal>DROP EXPRESSION IF EXISTS</literal> is specified and the
       column is not a stored generated column, no error is thrown.  In this
       case a notice is issued instead.
      </para>
+
+     <note>
+      <para>
+       Turning a column into generated or changing the generation expression
+       is currently not supported.  To do that, use <command>DROP COLUMN</command>
+       and <command>CREATE COLUMN</command> with <command>GENERATED ALWAYS AS ( generation_expr ) STORED</command>.
+      </para>
+     </note>
     </listitem>
    </varlistentry>
 
    <varlistentry>
     <term><literal>ADD GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY</literal></term>
     <term><literal>SET GENERATED { ALWAYS | BY DEFAULT }</literal></term>
     <term><literal>DROP IDENTITY [ IF EXISTS ]</literal></term>
     <listitem>
      <para>
       These forms change whether a column is an identity column or change the
-- 
2.30.1 (Apple Git-130)

reply

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: [email protected]
  Cc: [email protected]
  Subject: Re: Note that ALTER TABLE cannot alter generated stored columns
  In-Reply-To: <CAE2gYzw0dUtL4PZ6_zfwyF9yCpejuRMWh_9gFgf3s8YEqxCN0Q@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox