如果 PostgreSQL 上不存在，如何添加列？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12597465/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to add column if not exists on PostgreSQL?
提问by marioosh
采纳答案by Matthew Wood
Here's a short-and-sweet version using the "DO" statement:
这是使用“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;
$$
You can't pass these as parameters, you'll need to do variable substitution in the string on the client side, but this is a self contained query that only emits a message if the column already exists, adds if it doesn't and will continue to fail on other errors (like an invalid data type).
您不能将这些作为参数传递，您需要在客户端的字符串中进行变量替换，但这是一个自包含查询，仅在列已存在时才发出消息，如果不存在则添加将继续因其他错误（如无效数据类型）而失败。
I don't recommend doing ANY of these methods if these are random strings coming from external sources. No matter what method you use (cleint-side or server-side dynamic strings executed as queries), it would be a recipe for disaster as it opens you to SQL injection attacks.
如果这些是来自外部来源的随机字符串，我不建议执行任何这些方法。无论您使用什么方法（客户端或服务器端作为查询执行的动态字符串），它都会导致灾难，因为它使您面临 SQL 注入攻击。
回答by a_horse_with_no_name
With Postgres 9.6this can be done using the option if not exists
使用Postgres 9.6这可以使用选项来完成if not exists
ALTER TABLE table_name ADD COLUMN IF NOT EXISTS column_name INTEGER;
回答by Erwin Brandstetter
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;
Call:
称呼：
SELECT f_add_col('public.kat', 'pfad1', 'int');
Returns TRUEon success, else FALSE(column already exists).
Raises an exception for invalid table or type name.
TRUE成功返回，否则返回FALSE（列已存在）。
引发无效表或类型名称的异常。
Why another version?
为什么是另一个版本？
This could be done with a
DOstatement, butDOstatements cannot return anything. And if it's for repeated use, I would create a function.I use the object identifier types
regclassandregtypefor_tbland_typewhich a) prevents SQL injection and b) checks validity of both immediately (cheapest possible way). The column name_colhas still to be sanitized forEXECUTEwithquote_ident(). More explanation in this related answer:format()requires Postgres 9.1+. For older versions concatenate manually:EXECUTE 'ALTER TABLE ' || _tbl || ' ADD COLUMN ' || quote_ident(_col) || ' ' || _type;You can schema-qualify your table name, but you don't have to.
You can double-quote the identifiers in the function call to preserve camel-case and reserved words (but you shouldn't use any of this anyway).I query
pg_cataloginstead of theinformation_schema. Detailed explanation:Blocks containing an
EXCEPTIONclause like the currently accepted answerare substantially slower. This is generally simpler and faster. The documentation:
这可以通过
DO语句完成，但DO语句不能返回任何内容。如果要重复使用，我会创建一个函数。我使用对象标识符类型
regclass和regtypefor_tbland_typewhich a) 防止 SQL 注入和 b) 立即检查两者的有效性（最便宜的方式）。列名_col仍需EXECUTE使用quote_ident(). 此相关答案中的更多解释：format()需要 Postgres 9.1+。对于旧版本手动连接：EXECUTE 'ALTER TABLE ' || _tbl || ' ADD COLUMN ' || quote_ident(_col) || ' ' || _type;您可以对表名进行架构限定，但并非必须如此。
您可以在函数调用中用双引号引用标识符以保留驼峰式大小写和保留字（但无论如何您都不应该使用任何一个）。我查询
pg_catalog而不是information_schema. 详细解释：
Tip: A block containing an
EXCEPTIONclause is significantly more expensive to enter and exit than a block without one. Therefore, don't useEXCEPTIONwithout need.
提示：包含
EXCEPTION子句的块比没有子句的块进入和退出的成本要高得多。因此，不要在EXCEPTION没有需要的情况下使用。
回答by Vivek S.
Following select query will return true/false, using EXISTS()function.
以下选择查询将返回true/false，使用EXISTS()函数。
EXISTS():
The argument of EXISTS is an arbitrary SELECT statement, or subquery. The subquery is evaluated to determine whether it returns any rows. If it returns at least one row, the result of EXISTS is "true"; if the subquery returns no rows, the result of EXISTS is "false"
EXISTS()：EXISTS
的参数是任意的 SELECT 语句或子查询。评估子查询以确定它是否返回任何行。如果至少返回一行，则 EXISTS 的结果为“true”；如果子查询没有返回任何行，则 EXISTS 的结果为“false”
SELECT EXISTS(
SELECT column_name
FROM information_schema.columns
WHERE table_schema='public'
and table_name='x'
and column_name='y')
and use the following dynamic sql statement to alter your table
并使用以下动态 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
$$
回答by Leon
For those who use Postgre 9.5+(I believe most of you do), there is a quite simple and clean solution
对于那些使用 Postgre 9.5+（我相信你们大多数人都这样做）的人，有一个非常简单和干净的解决方案
ALTER TABLE if exists <tablename> add if not exists <columnname> <columntype>
回答by solaimuruganv
the below function will check the column if exist return appropriate message else it will add the column to the table.
下面的函数将检查该列是否存在返回适当的消息，否则它将将该列添加到表中。
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;
$$
回答by David S
This is basically the solution from sola, but just cleaned up a bit. It's different enough that I didn't just want to "improve" his solution (plus, I sort of think that's rude).
这基本上是来自 sola 的解决方案，但只是清理了一下。这已经足够不同了，我不只是想“改进”他的解决方案（另外，我觉得这很粗鲁）。
Main difference is that it uses the EXECUTE format. Which I think is a bit cleaner, but I believe means that you must be on PostgresSQL 9.1 or newer.
主要区别在于它使用 EXECUTE 格式。我认为这更简洁一些，但我相信这意味着您必须使用 PostgresSQL 9.1 或更新版本。
This has been tested on 9.1 and works. Note: It will raise an error if the schema/table_name/or data_type are invalid. That could "fixed", but might be the correct behavior in many cases.
这已经在 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';
usage:
用法：
select add_column('public', 'foo', 'bar', 'varchar(30)');
回答by parthivrshah
You can do it by following way.
您可以通过以下方式进行。
ALTER TABLE tableName drop column if exists columnName;
ALTER TABLE tableName ADD COLUMN columnName character varying(8);
So it will drop the column if it is already exists. And then add the column to particular table.
因此，如果该列已经存在，它将删除该列。然后将该列添加到特定表中。
回答by user645527
Can be added to migration scripts invoke function and drop when done.
可以添加到迁移脚本调用函数并在完成后删除。
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();
回答by ThinkBonobo
In my case, for how it was created reason it is a bit difficult for our migration scripts to cut across different schemas.
就我而言，由于它是如何创建的，我们的迁移脚本很难跨越不同的模式。
To work around this we used an exception that just caught and ignored the error. This also had the nice side effect of being a lot easier to look at.
为了解决这个问题，我们使用了一个刚刚捕获并忽略错误的异常。这也有一个很好的副作用，即更容易查看。
However, be wary that the other solutions have their own advantages that probably outweigh this solution:
但是，请注意其他解决方案的自身优势可能超过此解决方案：
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 $$;
