仅当存在时重命名列

Question

PostgreSQL不允许

ALTER TABLE t RENAME COLUMN IF EXISTS c1 TO c2

有时候我们需要修改数据库结构，但是每次都需要手动检查是否已经执行过，这样很不方便。因此编写可以重复运行的脚本来修改数据库结构非常实用。

那么我该如何编写一个 PostgreSQL 函数来实现这个功能呢？

- NessBird

---

关于plpgsql函数，先检查information_schema，然后执行动态查询以重命名列，您怎么看？ - Tomasz Myrta

谢谢您的提问。如何将需求发送给PostgreSQL开发人员？ - Charlie 木匠

Answer 1

请阅读codingvila.com上的文章，了解如何在PostgreSQL中仅在列存在时重命名列。重命名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 $$;

- Nikunj Satasiya

---

这很整洁...也许我会将其编写为一个函数并使用它。 - NessBird

@NessBird，如果您已经得到了解决方案，能否请您接受这个答案呢？这样其他人就可以查看被接受的答案并快速获得解决方案。 - Nikunj Satasiya

Answer 2

您可以简单地处理可能在匿名代码块中引发的错误：

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之后的错误名称）。确保您使用的是适合您数据库版本的软件。

- Stephen Carboni

Answer 3

最好有两个函数，一个调用另一个:

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

Answer 4

@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$;

我添加了schema参数来处理同一表名的多个模式。 rename_column_if_exists除了可能添加schema之外，其余部分都保持不变。

- Belayer

---

你不需要一个返回语句吗？ - NessBird

NO。参见语言规范-“SQL”。最后一个语句是SQL函数必须是带有返回子句的选择或DML语句。它返回语句的结果。想一想，我不认为SQL函数可以有返回值 - 但不确定百分之百。 - Belayer

Answer 5

感谢大家提供的建议！ 我已经编写了一个可用于运行迁移的生产就绪解决方案。我使用相同的函数来检查列是否存在，但我已经用一个过程替换了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>);

- David Filat