如果PostgreSQL中不存在该如何添加列？ [英] How to add column if not exists on PostgreSQL?

问题描述

问题很简单。如何将列 x 添加到表 y ，但仅当 x 列不存在？我发现只有解决方案这里如何检查列是否存在。

Question is simple. How to add column x to table y, but only when x column doesn't exist ? I found only solution here how to check if column exists.

SELECT column_name 
FROM information_schema.columns 
WHERE table_name='x' and column_name='y';

推荐答案

以下是使用 DO语句：

Here's a short-and-sweet version using the "DO" statement:

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

如果这些方法是来自外部来源的随机字符串，则我不建议使用其中任何一种方法。无论您使用哪种方法（作为查询执行的服务器端或服务器端动态字符串），这都将是灾难的根源，因为它使您容易受到SQL注入攻击。

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.

这篇关于如果PostgreSQL中不存在该如何添加列？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！