PostgreSQL 中如何添加一个新的字段?

如何添加字段 x 到 y 表中，而且只有当x列不存在的时候才添加? 我发现只有解决方案这里列出了如何检查列是否存在。

SELECT column_name
FROM information_schema.columns
WHERE table_name='x' and column_name='y';
190596 次浏览
小开

只需检查查询是否返回了一个column_name。

如果不是，执行如下命令:

ALTER TABLE x ADD COLUMN y int;

你把一些有用的东西放在x和y上，当然还有一个合适的数据类型，我用的是int。

小开

下面的函数将检查列是否存在，如果返回适当的消息，否则它将把列添加到表中。

create or replace function addcol(schemaname varchar, tablename varchar, colname varchar, coltype varchar)
returns varchar
language 'plpgsql'
as
$$
declare
col_name varchar ;
begin
execute 'select column_name from information_schema.columns  where  table_schema = ' ||
quote_literal(schemaname)||' and table_name='|| quote_literal(tablename) || '   and    column_name= '|| quote_literal(colname)
into   col_name ;


raise info  ' the val : % ', col_name;
if(col_name is null ) then
col_name := colname;
execute 'alter table ' ||schemaname|| '.'|| tablename || ' add column '|| colname || '  ' || coltype;
else
col_name := colname ||' Already exist';
end if;
return col_name;
end;
$$
小开

这基本上是从sola的解决方案，但只是清理了一点。这是足够不同的，我不只是想“改进”他的解决方案(另外，我觉得这有点粗鲁)。

主要区别是它使用EXECUTE格式。我认为这是一点干净，但我相信这意味着你必须使用PostgresSQL 9.1或更新版本。

这已经在9.1测试，并工作。注意:如果schema/table_name/或data_type无效，将引发错误。这可能是“固定的”，但在许多情况下可能是正确的行为。

CREATE OR REPLACE FUNCTION add_column(schema_name TEXT, table_name TEXT,
column_name TEXT, data_type TEXT)
RETURNS BOOLEAN
AS
$BODY$
DECLARE
_tmp text;
BEGIN


EXECUTE format('SELECT COLUMN_NAME FROM information_schema.columns WHERE
table_schema=%L
AND table_name=%L
AND column_name=%L', schema_name, table_name, column_name)
INTO _tmp;


IF _tmp IS NOT NULL THEN
RAISE NOTICE 'Column % already exists in %.%', column_name, schema_name, table_name;
RETURN FALSE;
END IF;


EXECUTE format('ALTER TABLE %I.%I ADD COLUMN %I %s;', schema_name, table_name, column_name, data_type);


RAISE NOTICE 'Column % added to %.%', column_name, schema_name, table_name;


RETURN TRUE;
END;
$BODY$
LANGUAGE 'plpgsql';

用法:

select add_column('public', 'foo', 'bar', 'varchar(30)');
小开
最佳答案

这里有一个简短而甜蜜的版本，使用“DO"声明:

DO $$
BEGIN
BEGIN
ALTER TABLE <table_name> ADD COLUMN <column_name> <column_type>;
EXCEPTION
WHEN duplicate_column THEN RAISE NOTICE 'column <column_name> already exists in <table_name>.';
END;
END;
$$

您不能将这些作为参数传递，您需要在客户端对字符串进行变量替换，但这是一个自包含查询，仅在列已经存在时发出消息，如果不存在则添加，并且在其他错误(如无效数据类型)时将继续失败。

如果这些是来自外部来源的随机字符串，我不建议使用任何这些方法。无论您使用什么方法(作为查询执行的客户端或服务器端动态字符串)，它都会导致灾难，因为它使您容易受到SQL注入攻击。

小开
< p > Postgres 9.6 added ALTER TABLE tbl ADD COLUMN IF NOT EXISTS column_name。
所以这已经过时了。您可以在较旧的版本或变体中使用它来检查列名以外的其他内容 
CREATE OR REPLACE function f_add_col(_tbl regclass, _col  text, _type regtype)
RETURNS bool
LANGUAGE plpgsql AS
$func$
BEGIN
IF EXISTS (SELECT FROM pg_attribute
WHERE  attrelid = _tbl
AND    attname  = _col
AND    NOT attisdropped) THEN
RETURN false;
ELSE
EXECUTE format('ALTER TABLE %s ADD COLUMN %I %s', _tbl, _col, _type);
RETURN true;
END IF;
END
$func$;

电话:

SELECT f_add_col('public.kat', 'pfad1', 'int');

成功返回true，否则false(列已经存在)。
对于无效的表名或类型名引发异常

为什么是另一个版本?

  • 这可以用DO语句来完成，但是DO语句不能返回任何东西。如果是为了重复使用，我会创建一个函数。

  • 我使用对象标识符类型 regclass和regtype用于_tbl和_type，其中a)防止SQL注入和b)立即检查两者的有效性(最便宜的方式)。列名_col仍然需要用quote_ident()对EXECUTE进行净化。看到的:

  • format()需要Postgres 9.1+对于旧版本手动连接:

      EXECUTE 'ALTER TABLE ' || _tbl || ' ADD COLUMN ' || quote_ident(_col) || ' ' || _type;
    • 可以对表名进行模式限定，但不必这样做。
    您可以在函数调用中对标识符进行双引号，以保留驼峰式大小写和保留字(但无论如何您都不应该使用其中任何一个)

  • 我查询的是pg_catalog而不是information_schema。详细解释:

    • 包含EXCEPTION子句的块实际上更慢。
    这样更简单快捷。手册: < / p >

提示

包含EXCEPTION子句的块明显更大 进出的成本比一个没有的街区要高。 因此，如果没有必要，不要使用EXCEPTION
小开

可以添加到迁移脚本调用函数，并在完成时删除。

create or replace function patch_column() returns void as
$$
begin
if exists (
select * from information_schema.columns
where table_name='my_table'
and column_name='missing_col'
)
then
raise notice 'missing_col already exists';
else
alter table my_table
add column missing_col varchar;
end if;
end;
$$ language plpgsql;


select patch_column();


drop function if exists patch_column();
小开

下面的选择查询将返回true/false，使用EXISTS()函数。

EXISTS的实参是一个任意的SELECT语句，或 子查询。计算子查询以确定它是否返回 任何行。如果它至少返回一行，则EXISTS的结果为 “true";如果子查询不返回任何行，EXISTS的结果为 “false" < / p > 
SELECT EXISTS(SELECT  column_name
FROM  information_schema.columns
WHERE  table_schema = 'public'
AND  table_name = 'x'
AND  column_name = 'y');

并使用下面的动态SQL语句修改表

DO
$$
BEGIN
IF NOT EXISTS (SELECT column_name
FROM  information_schema.columns
WHERE  table_schema = 'public'
AND  table_name = 'x'
AND  column_name = 'y') THEN
ALTER TABLE x ADD COLUMN y int DEFAULT NULL;
ELSE
RAISE NOTICE 'Already exists';
END IF;
END
$$
小开

你可以这样做。

ALTER TABLE tableName drop column if exists columnName;
ALTER TABLE tableName ADD COLUMN columnName character varying(8);

因此，如果该列已经存在，它将删除该列。然后将列添加到特定的表中。

小开

对于Postgres 9.6，可以使用选项if not exists来实现

ALTER TABLE table_name ADD COLUMN IF NOT EXISTS column_name INTEGER;
小开

在我的例子中，由于创建它的原因，我们的迁移脚本跨越不同的模式有点困难。

为了解决这个问题，我们使用了一个异常，它只是捕获并忽略了错误。这也有一个很好的副作用，就是更容易观察。

然而，要注意，其他解决方案都有自己的优势，可能会超过这个解决方案:

DO $$
BEGIN
BEGIN
ALTER TABLE IF EXISTS bobby_tables RENAME COLUMN "dckx" TO "xkcd";
EXCEPTION
WHEN undefined_column THEN RAISE NOTICE 'Column was already renamed';
END;
END $$;
小开

对于那些使用Postgre 9.5+的人(我相信你们大多数人都在使用)，有一个非常简单和干净的解决方案

ALTER TABLE if exists <tablename> add if not exists <columnname> <columntype>

提交答案