PostgreSQL 13: ALTER TABLE命令新增DROP EXPRESSION选项

Generated Columns特性是PostgreSQL 12 版本新增的，支持定义表的字段为Generated Columns，其值依赖于根据其它字段进行表达式计算。

之前写了篇博客介绍Generated Columns特性，详见: PostgreSQL 12: 支持 Generated Columns 特性

如果表定义了Generated Columns字段，PostgreSQL 12 版本无法删除字段的Generated Columns属性，13 版本的ALTER TABLE命令新增DROP EXPRESSION选项可删除字段的Generated Columns属性。

手册说明

Add ALTER TABLE clause DROP EXPRESSION to remove generated properties from columns (Peter Eisentraut)

关于DROP EXPRESSION

ALTER TABLE的DROP EXPRESSION选项手册说明如下:

DROP EXPRESSION [ IF EXISTS ]
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.

If DROP EXPRESSION IF EXISTS is specified and the column is not a stored generated column, no error is thrown. 
In this case a notice is issued instead

DROP EXPRESSION将generated column转换成普通列，原generated column列中的数据依然保留，此列的新数据将不再采用表达式生成。

PostgreSQL 12测试

创建测试表，字段sum_score为generated column，如下:

postgres=# CREATE TABLE score(stuid int4, chinese int2, math int2, sum_score int2 GENERATED ALWAYS AS (chinese+math) STORED );
CREATE TABLE

postgres=# \d score
                                   Table "public.score"
  Column   |   Type   | Collation | Nullable |                   Default
-----------+----------+-----------+----------+---------------------------------------------
 stuid     | integer  |           |          |
 chinese   | smallint |           |          |
 math      | smallint |           |          |
 sum_score | smallint |           |          | generated always as (chinese + math) stored

postgres=# INSERT INTO score(stuid,chinese,math) VALUES(1,90,95);
INSERT 0 1

postgres=# SELECT * FROM score;
 stuid | chinese | math | sum_score
-------+---------+------+-----------
     1 |      90 |   95 |       185
(1 row)

尝试删除sum_score字段的generated属性，如下:

postgres=# ALTER TABLE score ALTER COLUMN sum_score DROP DEFAULT;
ERROR:  column "sum_score" of relation "score" is a generated column

12 版本不支持删除字段的Generated Columns属性。

PostgreSQL 13测试

创建测试表，字段sum_score为generated column，如下:

postgres=# CREATE TABLE score(stuid int4, chinese int2, math int2, sum_score int2 GENERATED ALWAYS AS (chinese+math) STORED );
CREATE TABLE

postgres=# INSERT INTO score(stuid,chinese,math) VALUES(1,90,95);
INSERT 0 1

postgres=# SELECT * FROM score;
 stuid | chinese | math | sum_score
-------+---------+------+-----------
     1 |      90 |   95 |       185
(1 row)

postgres=# \d score
                                   Table "public.score"
  Column   |   Type   | Collation | Nullable |                   Default
-----------+----------+-----------+----------+---------------------------------------------
 stuid     | integer  |           |          |
 chinese   | smallint |           |          |
 math      | smallint |           |          |
 sum_score | smallint |           |          | generated always as (chinese + math) stored

尝试删除sum_score字段的generated属性，如下:

postgres=# ALTER TABLE score ALTER COLUMN sum_score DROP EXPRESSION ;
ALTER TABLE

postgres=# \d score
                 Table "public.score"
  Column   |   Type   | Collation | Nullable | Default
-----------+----------+-----------+----------+---------
 stuid     | integer  |           |          |
 chinese   | smallint |           |          |
 math      | smallint |           |          |
 sum_score | smallint |           |          |

发现sum_score字段的generated属性已被删除，sum_score字段转变成了普通字段。

继续插入数据验证，如下:

postgres=# INSERT INTO score(stuid,chinese,math) VALUES(1,100,200);
INSERT 0 1

postgres=# SELECT *FROM score;
 stuid | chinese | math | sum_score
-------+---------+------+-----------
     1 |      90 |   95 |       185
     1 |     100 |  200 |
(2 rows)

发现sum_score字段为空，符合测试预期。

参考

• PostgreSQL 13 ALTER TABLE
• PostgreSQL 12 ALTER TABLE
• https://postgres.fun/20190722112100.html
• Waiting for PostgreSQL 13 – ALTER TABLE … ALTER COLUMN … DROP EXPRESSION