PostgreSQL中Alter语句可重复执行

1 问题描述

PostgreSQL中通过Alter对表的修改不支持IF NOT EXISTS子句来达成可重复执行的。
例如：

ALTER TABLE "public"."table_name" 
ADD PRIMARY KEY IF NOT EXISTS ("column1", "column2");

上述sql会报如下错误
[Err] ERROR: syntax error at or near “IF”
LINE 2: ADD PRIMARY KEY IF NOT EXISTS (“xxx”, “xxx”);

2 解决方法

DO
$do$
BEGIN
	IF NOT EXISTS(select constraint_name from information_schema.table_constraints where table_name = 'table_name' and constraint_type = 'PRIMARY KEY') 
	THEN 
		ALTER TABLE "public"."table_name" 
		ALTER COLUMN "xxx" SET NOT NULL,
		ALTER COLUMN "xxx" SET NOT NULL, 
		ADD PRIMARY KEY("xxx", "xxx");
	END IF;
END
$do$

也可以写成下方这种：

DO $$
BEGIN
	IF NOT EXISTS(select constraint_name from information_schema.table_constraints where table_name = 'table_name' and constraint_type = 'PRIMARY KEY')
	THEN
		ALTER TABLE "public"."table_name"
		ALTER COLUMN "xxx" SET NOT NULL,
		ALTER COLUMN "xxx" SET NOT NULL,
		ADD PRIMARY KEY("xxx", "xxx");
	END IF;
END $$

还有一种写法，不需要if，then，只需要在添加主键前删除一遍（删除支持if exists）即可。

-- 删除主键
ALTER TABLE "public"."table_name" DROP constraints if exists constraints_name;
ALTER TABLE "public"."table_name" 
ALTER COLUMN "xxx" SET NOT NULL,
ALTER COLUMN "xxx" SET NOT NULL, 
ADD PRIMARY KEY("xxx", "xxx");