PostgreSQL不允许
ALTER TABLE t RENAME COLUMN IF EXISTS c1 TO c2
有时候我们需要修改数据库结构,但是每次都需要手动检查是否已经执行过,这样很不方便。因此编写可以重复运行的脚本来修改数据库结构非常实用。
那么我该如何编写一个 PostgreSQL 函数来实现这个功能呢?
PostgreSQL不允许
ALTER TABLE t RENAME COLUMN IF EXISTS c1 TO c2
有时候我们需要修改数据库结构,但是每次都需要手动检查是否已经执行过,这样很不方便。因此编写可以重复运行的脚本来修改数据库结构非常实用。
那么我该如何编写一个 PostgreSQL 函数来实现这个功能呢?
DO $$
BEGIN
IF EXISTS(SELECT *
FROM information_schema.columns
WHERE table_name='your_table' and column_name='your_column')
THEN
ALTER TABLE "public"."your_table" RENAME COLUMN "your_column" TO "your_new_column";
END IF;
END $$;
您可以简单地处理可能在匿名代码块中引发的错误:
DO
$$
BEGIN
ALTER TABLE t
RENAME COLUMN c1 TO c2;
EXCEPTION
WHEN undefined_column THEN RAISE NOTICE 'column t.c1 does not exist';
END;
$$;
您可以省略THEN后面的文本以什么都不做:
DO
$$
BEGIN
ALTER TABLE t
RENAME COLUMN c1 TO c2;
EXCEPTION
WHEN undefined_column THEN
END;
$$;
当发生错误时,您可能只会得到一个数字。您可以从这里找到条件名称(在WHEN之后的错误名称)。确保您使用的是适合您数据库版本的软件。
CREATE OR REPLACE FUNCTION column_exists(ptable TEXT, pcolumn TEXT)
RETURNS BOOLEAN AS $BODY$
DECLARE result bool;
BEGIN
-- Does the requested column exist?
SELECT COUNT(*) INTO result
FROM information_schema.columns
WHERE
table_name = ptable and
column_name = pcolumn;
RETURN result;
END$BODY$
LANGUAGE plpgsql VOLATILE;
CREATE OR REPLACE FUNCTION rename_column_if_exists(ptable TEXT, pcolumn TEXT, new_name TEXT)
RETURNS VOID AS $BODY$
BEGIN
-- Rename the column if it exists.
IF column_exists(ptable, pcolumn) THEN
EXECUTE FORMAT('ALTER TABLE %I RENAME COLUMN %I TO %I;',
ptable, pcolumn, new_name);
END IF;
END$BODY$
LANGUAGE plpgsql VOLATILE;
@NessBird 的两种函数方法都不错,但 Column_Exists 函数可以简化为 select exists,避免计数,并且作为 SQL 函数而非 plpgsql 函数。
create or replace function
column_exists(ptable text, pcolumn text, pschema text default 'public')
returns boolean
language sql stable strict
as $body$
-- does the requested table.column exist in schema?
select exists
( select null
from information_schema.columns
where table_name=ptable
and column_name=pcolumn
and table_schema=pschema
);
$body$;
感谢大家提供的建议!
我已经编写了一个可用于运行迁移的生产就绪解决方案。我使用相同的函数来检查列是否存在,但我已经用一个过程替换了rename_table_if_exists
函数:
CREATE OR REPLACE FUNCTION "product".column_exists (ptable text, pcolumn text, pschema text DEFAULT 'public')
RETURNS boolean
LANGUAGE sql
STABLE STRICT
AS $BODY$
-- does the requested table.column exist in schema?
SELECT
EXISTS (
SELECT
NULL
FROM
information_schema.columns
WHERE
table_name = ptable
AND column_name = pcolumn
AND table_schema = pschema);
$BODY$;
CREATE OR REPLACE PROCEDURE "product".rename_column_if_exists (ptable TEXT, pcolumn TEXT, new_name TEXT, pschema text DEFAULT 'public')
LANGUAGE plpgsql
AS $$
BEGIN
-- Rename the column if it exists.
IF product.column_exists (ptable,
pcolumn,
pschema) THEN
EXECUTE FORMAT('ALTER TABLE %I.%I RENAME COLUMN %I TO %I;', pschema, ptable, pcolumn, new_name);
END IF;
END
$$;
它可以像这样使用:
CALL <schema?>.rename_column_if_exists ('users', 'name', 'first_name', <schema name - optional>);