blocks|key|253003|text|下面是使用"DO“语句的简短版本：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|253004|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;
$$|code-block|syntax|javascript|253005|您不能将这些作为参数传递，您需要在客户端的字符串中进行变量替换，但这是一个自包含查询，它只在列已经存在时发出消息，如果列不存在则添加，并且在出现其他错误(如无效的数据类型)时将继续失败。|253006|如果这些是来自外部来源的随机字符串，我不推荐使用这些方法中的任何一种。无论您使用哪种方法(作为查询执行的客户端或服务器端动态字符串)，它都会给您带来灾难，因为它会使您遭受SQL注入攻击。|253007|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|N|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|O|8|@]|9|@]|A|$]]|$1|I|3|J|5|6|7|P|8|@]|9|@]|A|$]]|$1|K|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|L|$]]

Here's a short-and-sweet version using the &quot;DO&quot; statement:
<pre><code>DO $$ 
 BEGIN
 BEGIN
 ALTER TABLE &lt;table_name&gt; ADD COLUMN &lt;column_name&gt; &lt;column_type&gt;;
 EXCEPTION
 WHEN duplicate_column THEN RAISE NOTICE 'column &lt;column_name&gt; already exists in &lt;table_name&gt;.';
 END;
 END;
$$
</code></pre>
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 (client-side or server-side dynamic strings executed as queries), it would be a recipe for disaster as it opens you to SQL injection attacks.

blocks|key|470052|text|对于Postgres+9.6，可以使用选项if+not+exists来完成此操作|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|470053|ALTER+TABLE+table_name+ADD+COLUMN+IF+NOT+EXISTS+column_name+INTEGER;|code-block|syntax|javascript|470054|entityMap|0|LINK|mutability|MUTABLE|url|https://www.postgresql.org/docs/9.6/static/sql-altertable.html^0|L|D|2|C|0|0|0^^$0|@$1|2|3|4|5|6|7|S|8|@$9|T|A|U|B|C]]|D|@$9|V|A|W|1|X]]|E|$]]|$1|F|3|G|5|H|7|Y|8|@]|D|@]|E|$I|J]]|$1|K|3|-4|5|6|7|Z|8|@]|D|@]|E|$]]]|L|$M|$5|N|O|P|E|$Q|R]]]]

With <a href="https://www.postgresql.org/docs/9.6/static/sql-altertable.html" rel="noreferrer">Postgres 9.6</a> this can be done using the option <code>if not exists</code>

<pre><code>ALTER TABLE table_name ADD COLUMN IF NOT EXISTS column_name INTEGER;
</code></pre>

blocks|key|470132|text|对于那些使用Postgre+9.5%2B的人(我相信你们中的大多数人都这样做)，有一个非常简单干净的解决方案。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|470133|ALTER+TABLE+if+exists+<tablename>+add+if+not+exists+<columnname>+<columntype>|code-block|syntax|javascript|470134|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

For those who use Postgre 9.5+(I believe most of you do), there is a quite simple and clean solution

<pre><code>ALTER TABLE if exists &lt;tablename&gt; add if not exists &lt;columnname&gt; &lt;columntype&gt;
</code></pre>

blocks|key|469794|text|下面的函数将检查该列，如果存在，则返回适当的消息，否则它会将该列添加到表中。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|469795|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+=+'+%7C%7C
++++++quote_literal(schemaname)%7C%7C'+and+table_name='%7C%7C+quote_literal(tablename)+%7C%7C+'+++and++++column_name=+'%7C%7C+quote_literal(colname)++++
++++++into+++col_name+;+++

++++++raise+info++'+the+val+:+%25+',+col_name;
++++++if(col_name+is+null+)+then+
++++++++++col_name+:=+colname;
++++++++++execute+'alter+table+'+%7C%7Cschemaname%7C%7C+'.'%7C%7C+tablename+%7C%7C+'+add+column+'%7C%7C+colname+%7C%7C+'++'+%7C%7C+coltype;+
++++++else
+++++++++++col_name+:=+colname+%7C%7C'+Already+exist';
++++++end+if;
return+col_name;
end;
$$|code-block|syntax|javascript|469796|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

the below function will check the column if exist return appropriate message else it will add the column to the table. 

<pre><code>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;
$$
</code></pre>

blocks|key|469848|text|这基本上是来自sola的解决方案，但只是稍微清理了一下。这已经足够不同了，我并不只是想“改进”他的解决方案(另外，我觉得这很粗鲁)。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|469849|主要区别在于它使用的是EXECUTE格式。我认为这更简洁一些，但我认为这意味着你必须使用PostgresSQL+9.1或更高版本。|469850|这已经在9.1上进行了测试，并且工作正常。注意:如果schema/table_name/或data_type无效，则会引发错误。这可以“修复”，但在许多情况下可能是正确的行为。|469851|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=%25L
++++AND+table_name=%25L
++++AND+column_name=%25L',+schema_name,+table_name,+column_name)
++INTO+_tmp;

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

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

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

++RETURN+TRUE;
END;
$BODY$
LANGUAGE+'plpgsql';|code-block|syntax|javascript|469852|用法：|469853|select+add_column('public',+'foo',+'bar',+'varchar(30)');|469854|entityMap^0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|Q|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|R|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|S|8|@]|9|@]|A|$]]|$1|F|3|G|5|H|7|T|8|@]|9|@]|A|$I|J]]|$1|K|3|L|5|6|7|U|8|@]|9|@]|A|$]]|$1|M|3|N|5|H|7|V|8|@]|9|@]|A|$I|J]]|$1|O|3|-4|5|6|7|W|8|@]|9|@]|A|$]]]|P|$]]

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). 

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.

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.

<pre><code>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';
</code></pre>

usage:

<pre><code>select add_column('public', 'foo', 'bar', 'varchar(30)');
</code></pre>

blocks|key|469967|text|可以添加到迁移脚本中，调用函数并在完成时删除。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|469968|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();|code-block|syntax|javascript|469969|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Can be added to migration scripts invoke function and drop when done.

<pre><code>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();
</code></pre>

blocks|key|253145|text|在我的例子中，由于创建方式的原因，我们的迁移脚本很难跨越不同的模式。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|253146|为了解决这个问题，我们使用了一个刚刚捕获并忽略错误的异常。这也有一个很好的副作用，那就是看起来容易得多。|253147|但是，请注意，其他解决方案都有自己的优势，这些优势可能会超过此解决方案：|253148|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+$$;|code-block|syntax|javascript|253149|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|N|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|O|8|@]|9|@]|A|$]]|$1|F|3|G|5|H|7|P|8|@]|9|@]|A|$I|J]]|$1|K|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|L|$]]

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:

<pre><code>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 $$;
</code></pre>

blocks|key|253073|text|你可以按照下面的方法来做。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|253074|ALTER+TABLE+tableName+drop+column+if+exists+columnName;+
ALTER+TABLE+tableName+ADD+COLUMN+columnName+character+varying(8);|code-block|syntax|javascript|253075|因此，如果该列已经存在，它将删除该列。然后将该列添加到特定表中。|253076|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

You can do it by following way.

<pre><code>ALTER TABLE tableName drop column if exists columnName; 
ALTER TABLE tableName ADD COLUMN columnName character varying(8);
</code></pre>

So it will drop the column if it is already exists. And then add the column to particular table.

blocks|key|252846|text|只需检查查询是否返回了column_name。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|252847|如果不是，执行如下代码：|252848|ALTER+TABLE+x+ADD+COLUMN+y+int;|code-block|syntax|javascript|252849|你把一些有用的东西放在'x‘和'y’的地方，当然还有一个合适的数据类型，我用的是int。|252850|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|N|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|O|8|@]|9|@]|A|$G|H]]|$1|I|3|J|5|6|7|P|8|@]|9|@]|A|$]]|$1|K|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|L|$]]

Simply check if the query returned a column_name.

If not, execute something like this:

<pre><code>ALTER TABLE x ADD COLUMN y int;
</code></pre>

Where you put something useful for 'x' and 'y' and of course a suitable datatype where I used int.

Question is simple. How to add column <code>x</code> to table <code>y</code>, but only when <code>x</code> column doesn't exist ? I found only solution <a href="https://stackoverflow.com/questions/9991043/how-can-i-test-if-a-column-exists-in-a-table-using-an-sql-statement">here</a> how to check if column exists.

<pre><code>SELECT column_name 
FROM information_schema.columns 
WHERE table_name='x' and column_name='y';
</code></pre>

How to add column if not exists on PostgreSQL?

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

问题很简单。如何将列x添加到表y，但仅当x列不存在时？我找到了唯一的解决方案如何检查列是否存在。SELECT column_name FROM information_schema.columns WHERE table_name='x' and column_name='y';

问如果PostgreSQL上不存在列，如何添加？
EN

回答 9

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如果PostgreSQL上不存在列，如何添加？EN

回答 9

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如果PostgreSQL上不存在列，如何添加？
EN