【Flyway】pgsql R__开头的sql

flyway R__开头的sql在每次部署时会执行一次
例如：R__UPDATE_20200909.sql

0 判断

-- 0.1 判断列是否存在
IF (select count(*) from information_schema.columns where table_name = '表名' and  column_name  = '列名') = 0
THEN
	...
END IF;

-- 0.2 判断列类型
IF (select data_type from information_schema.columns where table_name = '表名' and  column_name  = '列名') = '类型'
THEN
	...
END IF;

1 新增列

-- 列不存在时新增
ALTER TABLE 表名 ADD COLUMN IF NOT EXISTS 列名 类型;

-- 示例
ALTER TABLE record ADD COLUMN IF NOT EXISTS age int2;

2 判断并修改列类型，这里是int2改为int4。同类型扩展可以改，其他类型转换会报错。

DO
$do$
BEGIN
    IF (select data_type from information_schema.columns where table_name = 'record' and  column_name  = 'age') = 'smallint'
    THEN
        ALTER TABLE record ALTER age TYPE int4;
    END IF;
END
$do$
;

3 创建索引

-- 索引不存在时新增
CREATE INDEX IF NOT EXISTS 索引名 ON 表名(索引字段...);
CREATE INDEX IF NOT EXISTS 索引名 ON 表名 USING btree (索引字段...);

-- 示例，name和age的组合索引
CREATE INDEX IF NOT EXISTS idx_name_sex ON record(name, age);
CREATE INDEX IF NOT EXISTS idx_name_sex ON record USING btree (name, age);

4 创建表

-- 表不存在时创建
CREATE TABLE IF NOT EXISTS "record" (
	"name" varchar(25) NOT NULL,
	"age" int2 NOT NULL
);
COMMENT ON COLUMN "record"."name" IS "姓名";

5 删除列

-- 列存在时删除
ALTER TABLE 表名 DROP COLUMN IF EXISTS 列名;

-- 示例
ALTER TABLE record  DROP COLUMN IF EXISTS age;

6 改列名

DO
$do$
BEGIN
    IF (select count(*) from information_schema.columns where table_name = 'record' and  column_name  = 'name') = 0
    THEN
        -- ALTER TABLE 表名 RENAME 原名称 TO 新名称;
        ALTER TABLE record RENAME name TO new_name;
    END IF;
END
$do$
;

7 修改为非必填

ALTER TABLE 表名 ALTER 列名 DROP NOT NULL;

-- 示例
ALTER TABLE record ALTER name DROP NOT NULL;