On Wed, 2026-05-27 at 19:43 +0200, Alberto Piai wrote: > I'd like to take his point, together with the question from Tom and the > suggestion by Álvaro, and make a new proposal for the design of this > command. > > Design iteration 2 > ------------------ > > Syntax: > > ALTER TABLE t ALTER COLUMN c > ADD GENERATED ALWAYS AS (expr) STORED USING CONSTRAINT check_name > > check_name must be a valid constraint of the form > > CHECK (c IS NOT DISTINCT FROM (expr)) > > This fails if: > > - a check constraint named check_name is not found for table c > - the constraint is not valid > - the constraint does not match exactly the expr the user intends to use > as a stored default expression > > On success, the table c is now a stored generated column with the given > default expression, and the check_name constraint has been removed. > > This addresses Tom's remark, we can now fail instead of just rewriting > the column.
I like this proposal. It avoids the question "to rewrite or not to rewrite" by just outright failing if there is no suitable constraint. The idea to avoid the problem with NULL by forcing IS NOT DISTINCT FROM in the constraint is a good solution. Perhaps you could also allow the equality operator if the column in question is defined NOT NULL. > It improves slightly upon the issue of a potential conflict with a > future edition of the SQL standard, by being more specific. I don't see > a way to be completely sure we won't have conflicts. We could improve > more by making the syntax more "alien" and very unlinkely to be picked > up by the standard, but at a usability cost for Postgres. I'm open to > suggestions. I don't think that the new proposal makes it less likely to get in conflict with later additions to the standard. But I don't think that inventing unlikely syntax to avoid such conflicts is a good idea. First, the standard committee itself seems (or seemed) to have a strong predilection for alien, verbose syntax. Second, if we end up with weird, unwieldy syntax, that would be bad. No, the syntax you are proposing sounds reasonable to me. > It improves upon another question raised by Álvaro: does the user have > to clean up the constraint? In v1 I felt it was better to have the user > remove it after the migration. Since here it's explicitly mentioned as > the constraint to use to migrate the column, I think it's OK to remove > it. We are conceptually moving it from being a constraint to being the > new default expression. > > The implementation should also be simpler, since there will never be a > table rewrite. > > Any thoughts about this? Yes. I think that you should not drop the constraint. That's what I'd expect, similar to how we don't drop the check constraint that allows to skip the table scan in ALTER TABLE ... ALTER COLUMN ... SET NOT NULL or ALTER TABLE ... ATTACK PARTITION. I feel that automatically dropping the constraint is a bit too much black magic, but it is more a feeling than a conviction. Yours, Laurenz Albe
