PostgreSQL修改分区表字段为自增字段时出现语法错误求助

你好呀！我看了你遇到的问题，尝试把分区表的pk字段改成自增IDENTITY时出现了语法错误，咱们来一步步分析原因和解决办法。

首先，你的SQL语句本身存在语法问题：ALTER TABLE post_partitioned ALTER COLUMN pk TYPE int GENERATED ALWAYS AS IDENTITY 这种写法不符合PostgreSQL的语法规范。ALTER COLUMN ... TYPE 是用来修改字段数据类型的，而添加IDENTITY自增属性需要单独的语法逻辑，而且你的pk字段本来就是integer类型，TYPE int这部分完全是多余的。

不过更关键的限制是：PostgreSQL不允许在分区表的父表上直接定义IDENTITY列，因为父表本身并不存储实际数据，它只是一个分区的模板，IDENTITY属性是和数据存储实体绑定的，所以父表无法承载这个属性。

那该怎么实现分区表的自增主键呢？这里有两种实用的方案：

方案一：使用全局共享序列（推荐用于全局唯一的自增主键） #

1. 先创建一个全局序列：

CREATE SEQUENCE post_pk_seq;

2. 修改父表的pk字段，设置默认值为序列的下一个值：

ALTER TABLE post_partitioned ALTER COLUMN pk SET DEFAULT nextval('post_pk_seq');

3. 如果你需要像GENERATED ALWAYS那样禁止手动修改pk，可以添加约束来实现类似效果：

ALTER TABLE post_partitioned ALTER COLUMN pk SET NOT NULL;
ALTER TABLE post_partitioned ADD CONSTRAINT pk_no_manual_insert CHECK (pk = currval('post_pk_seq') OR pk IS NOT NULL);

如果允许手动插入特定pk值，可以跳过这一步。

方案二：在每个分区单独定义IDENTITY列 #

如果你希望每个分区拥有独立的自增序列，可以在创建分区时单独给pk字段添加IDENTITY属性：

CREATE TABLE post_partitioned_2023 PARTITION OF post_partitioned
FOR VALUES FROM ('2023-01-01') TO ('2024-01-01')
ALTER COLUMN pk ADD GENERATED ALWAYS AS IDENTITY;

不过这种方式下每个分区的pk会从1开始自增，可能出现全局重复的pk值，适合对pk全局唯一性要求不高的场景。

另外要注意你原来的表上有UNIQUE (pk, created_on)约束，这个在分区表上可以正常生效，不管用哪种方案，都不会影响这个约束的功能。最后提醒下，如果表中已有数据，修改字段前最好先备份数据，避免意外情况！

备注：内容来源于stack exchange，提问作者Alex