如何编写无需逐个添加增量变更的PostgreSQL表结构更新SQL脚本？

针对你的场景——用deb包分发应用，需要在postinst脚本里自动同步PostgreSQL表结构，不想维护一堆零散的增量脚本，这里有几个实用的解决方法：

1. 编写带条件判断的动态SQL脚本 #

不用单独管理每个版本的增量变更，直接维护一个包含当前完整表结构的脚本，每次运行都会自动补全缺失的表和列。

比如维护这样一个脚本，每次postinst执行它即可：

-- 先确保表存在，不存在则创建（包含当前所有基础列）
CREATE TABLE IF NOT EXISTS app_users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100)
);

-- 逐个检查并添加缺失的列
DO $$
BEGIN
    -- 检查full_name列是否存在，不存在则添加
    IF NOT EXISTS (
        SELECT 1 FROM information_schema.columns 
        WHERE table_name = 'app_users' AND column_name = 'full_name'
    ) THEN
        ALTER TABLE app_users ADD COLUMN full_name VARCHAR(100);
    END IF;

    -- 检查created_at列，不存在则添加并设置默认值
    IF NOT EXISTS (
        SELECT 1 FROM information_schema.columns 
        WHERE table_name = 'app_users' AND column_name = 'created_at'
    ) THEN
        ALTER TABLE app_users ADD COLUMN created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP;
    END IF;
END $$;

每次版本更新，你只需要修改这个脚本，把新增的列加到对应的检查块里即可，不用再单独写版本增量脚本。

2. 封装「创建或更新表」的PL/pgSQL函数 #

自己写一个可复用的函数，实现你想要的「表存在则补列，不存在则创建」的逻辑，后续更新结构只需要调用函数传参数就行。

示例函数：

CREATE OR REPLACE FUNCTION create_or_alter_table(
    p_table_name TEXT,
    p_columns TEXT[]
) RETURNS VOID AS $$
DECLARE
    v_column TEXT;
    v_existing_columns TEXT[];
BEGIN
    -- 先尝试创建表，已存在则跳过
    EXECUTE format('CREATE TABLE IF NOT EXISTS %I (id SERIAL PRIMARY KEY)', p_table_name);

    -- 获取当前表已有的列名
    SELECT array_agg(column_name) INTO v_existing_columns
    FROM information_schema.columns
    WHERE table_name = p_table_name;

    -- 遍历需要的列，缺啥补啥
    FOREACH v_column IN ARRAY p_columns LOOP
        IF v_column <> ALL(v_existing_columns) THEN
            EXECUTE format('ALTER TABLE %I ADD COLUMN %s', p_table_name, v_column);
        END IF;
    END LOOP;
END $$ LANGUAGE plpgsql;

调用方式：

SELECT create_or_alter_table(
    'app_users',
    ARRAY[
        'username VARCHAR(50) NOT NULL UNIQUE',
        'email VARCHAR(100)',
        'full_name VARCHAR(100)',
        'created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP'
    ]
);

以后更新表结构，只需要修改调用函数时的列数组即可，不用重复写ALTER语句。

3. 采用声明式结构文件+脚本生成SQL #

如果嫌手写SQL麻烦，可以维护一个JSON/YAML格式的表结构定义文件，再用简单的脚本（比如Python、Bash）读取文件自动生成对应的条件SQL，最后在postinst里执行生成的SQL。

比如写一个schema.yaml：

tables:
  app_users:
    columns:
      - id: SERIAL PRIMARY KEY
      - username: VARCHAR(50) NOT NULL UNIQUE
      - email: VARCHAR(100)
      - full_name: VARCHAR(100)
      - created_at: TIMESTAMP DEFAULT CURRENT_TIMESTAMP

然后用Python脚本读取这个文件，生成类似第一种方法的SQL语句。这种方式适合表多、结构复杂的场景，每次更新结构只需要修改YAML文件即可。

注意事项 #

• 以上方法仅适合非破坏性变更：比如添加列、设置默认值；如果是删除列、修改列类型这类可能丢失数据的操作，还是需要单独处理，或者在脚本中添加更严谨的判断逻辑。
• 执行SQL的数据库用户需要拥有足够权限：比如ALTER TABLE、CREATE FUNCTION等操作的权限。
• 建议在postinst脚本中先备份数据库，再执行结构更新，避免出现问题无法回滚。

内容的提问来源于stack exchange，提问作者fa__