PostgreSQL中Alter语句可重复执行

我的Coder小屋

已于 2023-06-07 09:31:46 修改

阅读量1.2k

点赞数 2

文章标签： postgresql 数据库

于 2023-05-25 10:28:26 首次发布

本文链接：https://blog.youkuaiyun.com/qq_36744540/article/details/130861767

版权

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");