问题很简单 . 如何将列 x 添加到表 y ,但仅当 x 列不存在时?我发现只有解决方案here如何检查列是否存在 .
SELECT column_name
FROM information_schema.columns
WHERE table_name='x' and column_name='y';
9 回答
19
CREATE OR REPLACE function f_add_col(_tbl regclass, _col text, _type regtype)
RETURNS bool AS
$func$
BEGIN
IF EXISTS (SELECT 1 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$ LANGUAGE plpgsql;
呼叫:
SELECT f_add_col('public.kat', 'pfad1', 'int');
成功时返回 TRUE ,否则 FALSE (列已存在) . 引发无效表或类型名称的异常 .
为什么是另一个版本?
这可以使用 DO 语句完成,但 DO 语句不能返回任何内容 . 如果是重复使用,我会创建一个函数 .
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;
$$
99
可以添加到迁移脚本调用函数并在完成时删除 .
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();
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';
ALTER TABLE tableName drop column if exists columnName;
ALTER TABLE tableName ADD COLUMN columnName character varying(8);
因此,如果列已经存在,它将删除该列 . 然后将列添加到特定表 .
-4
只需检查查询是否返回了column_name .
如果没有,请执行以下操作:
ALTER TABLE x ADD COLUMN y int;
你为'x'和'y'放置一些有用的东西,当然还有我使用int的合适的数据类型 .
16
这是一个使用“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;
$$
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
$$
9 回答
呼叫:
成功时返回
TRUE
,否则FALSE
(列已存在) .引发无效表或类型名称的异常 .
为什么是另一个版本?
这可以使用
DO
语句完成,但DO
语句不能返回任何内容 . 如果是重复使用,我会创建一个函数 .我使用object identifier types
regclass
和regtype
用于_tbl
和_type
,a)防止SQL注入和b)立即检查两者的有效性(最便宜的方式) . 列名_col
仍然需要EXECUTE
与EXECUTE
进行清理 . 在这个相关答案中有更多解释:Table name as a PostgreSQL function parameter
format()需要Postgres 9.1 . 对于旧版本手动连接:
您可以对表名进行架构限定,但不必如此 .
您可以在函数调用中双引号标识符以保留驼峰大小写和保留字(但不管怎样,您都不应该使用任何此类标记符) .
我查询
pg_catalog
而不是information_schema
. 详细说明:How to check if a table exists in a given schema
包含像the currently accepted answer这样的
EXCEPTION
子句的块要慢得多 . 这通常更简单,更快捷 . The documentation:如果存在,则下面的函数将检查列是否返回相应的消息,否则它会将列添加到表中 .
可以添加到迁移脚本调用函数并在完成时删除 .
使用Postgres 9.6可以使用选项
if not exists
完成这基本上是来自sola的解决方案,但只是清理了一下 . 这是不同的,我不只是想“改善”他的解决方案(另外,我认为这是粗鲁的) .
主要区别在于它使用EXECUTE格式 . 我认为它有点清洁,但我相信你必须使用PostgresSQL 9.1或更新版本 .
这已经在9.1和测试中进行了测试 . 注意:如果schema / table_name /或data_type无效,则会引发错误 . 这可以“修复”,但在许多情况下可能是正确的行为 .
用法:
你可以通过以下方式完成 .
因此,如果列已经存在,它将删除该列 . 然后将列添加到特定表 .
只需检查查询是否返回了column_name .
如果没有,请执行以下操作:
你为'x'和'y'放置一些有用的东西,当然还有我使用int的合适的数据类型 .
这是一个使用“DO”语句的简短版本:
您不能将这些作为参数传递,您需要在客户端的字符串中进行变量替换,但这是一个自包含的查询,只有在列已经存在时才会发出消息,如果不存在则添加将继续失败其他错误(如无效的数据类型) .
如果这些是来自外部源的随机字符串,我建议不要执行任何这些方法 . 无论你使用什么方法(作为查询执行的cleint端或服务器端动态字符串),它都会成为灾难的一个因素,因为它会打开你的SQL注入攻击 .
以下select查询将使用EXISTS()函数返回
true/false
.并使用以下动态sql语句来更改表