ALTER COLUMN ... SET EXPRESSION to alter stored generated column's expression
amul sul <sulamul@gmail.com>
From: Amul Sul <sulamul@gmail.com>
To: PostgreSQL-development <pgsql-hackers@postgresql.org>
Date: 2023-08-02T10:35:22Z
Lists: pgsql-hackers
Commits
Same data as JSON:
GET /api/v1/messages/:b64id/commits
the thread's linked commits as JSON, with link sources.
API reference →
-
ALTER TABLE command to change generation expression
- 5d06e99a3cfc 17.0 landed
-
Refactor: separate function to find all objects depending on a column
- d4e66a39eb96 17.0 landed
-
Turn AT_PASS_* macros into an enum
- cea89c93a102 17.0 landed
Attachments
- v1-0002-Allow-to-change-generated-column-expression.patch (application/x-patch) patch v1-0002
- v1-0001-Prerequisite-changes-rename-functions-enum.patch (application/x-patch) patch v1-0001
Hi, Currently, we have an option to drop the expression of stored generated columns as: ALTER [ COLUMN ] column_name DROP EXPRESSION [ IF EXISTS ] But don't have support to update that expression. The attached patch provides that as: ALTER [ COLUMN ] column_name SET EXPRESSION expression Note that this form of ALTER is meant to work for the column which is already generated. It then changes the generation expression in the catalog and rewrite the table, using the existing table rewrite facilities for ALTER TABLE. Otherwise, an error will be reported. To keep the code flow simple, I have renamed the existing function that was in use for DROP EXPRESSION so that it can be used for SET EXPRESSION as well, which is a similar design as SET/DROP DEFAULT. I kept this renaming code changes in a separate patch to minimize the diff in the main patch. Demo: -- Create table CREATE TABLE t1 (x int, y int GENERATED ALWAYS AS (x * 2) STORED); INSERT INTO t1 VALUES(generate_series(1,3)); -- Check the generated data SELECT * FROM t1; x | y ---+--- 1 | 2 2 | 4 3 | 6 (3 rows) -- Alter the expression ALTER TABLE t1 ALTER COLUMN y SET EXPRESSION (x * 4); -- Check the new data SELECT * FROM t1; x | y ---+---- 1 | 4 2 | 8 3 | 12 (3 rows) Thank you. -- Regards, Amul Sul EDB: http://www.enterprisedb.com