From: Tom Lane <tgl@sss.pgh.pa.us>
To: pgp@gmx.it
cc: pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #17970: FEATURE REQUEST-allow re-adding GENERATED ALWAYS AS
 constraint to existing columns after removing it
In-reply-to: <17970-63daec21cbb50126@postgresql.org>
References: <17970-63daec21cbb50126@postgresql.org>
Comments: In-reply-to PG Bug reporting form <noreply@postgresql.org>
	message dated "Tue, 13 Jun 2023 13:45:55 -0000"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <1636673.1686669171.1@sss.pgh.pa.us>
Date: Tue, 13 Jun 2023 11:12:51 -0400
Message-ID: <1636674.1686669171@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/1636674.1686669171%40sss.pgh.pa.us>
Precedence: bulk

PG Bug reporting form <noreply@postgresql.org> writes:
> From what I know, it is possible to transform a GENERATED ALWAYS AS ..
> STORED column into a normal one, by using:
> ALTER TABLE items ALTER COLUMN transformed_id DROP EXPRESSION;
> But the reverse (i.e. re-adding the "GENERATED ALWAYS AS .. STORED" part to
> an existing column) is not possible.

Just drop the column and re-add it.  The system would have to recompute
all the entries anyway, so I can't get excited about offering a shortcut.

> I think this is a contradiction, because, by modifying the underlying
> function, I am able to insert incoherent values in the generated column,

As David mentioned, that is not considered a supported thing.  We cannot
enforce it (look up the "halting problem" sometime), but if you change
an immutable function's behavior then ensuing problems are your fault
not Postgres'.

			regards, tom lane