首页 文章

如果在PostgreSQL上不存在列,如何添加列?

提问于
浏览
93

问题很简单 . 如何将列 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 语句不能返回任何内容 . 如果是重复使用,我会创建一个函数 .

    • 我使用object identifier types regclassregtype 用于 _tbl_type ,a)防止SQL注入和b)立即检查两者的有效性(最便宜的方式) . 列名 _col 仍然需要 EXECUTEEXECUTE 进行清理 . 在这个相关答案中有更多解释:

    • Table name as a PostgreSQL function parameter

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

    EXECUTE 'ALTER TABLE ' || _tbl || ' ADD COLUMN ' || quote_ident(_col) || ' ' || _type;
    

    提示:包含EXCEPTION子句的块比没有EXCEPTION子句的块更昂贵 . 因此,请勿在不需要的情况下使用EXCEPTION .

  • 1

    如果存在,则下面的函数将检查列是否返回相应的消息,否则它会将列添加到表中 .

    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();
    
  • 0

    使用Postgres 9.6可以使用选项 if not exists 完成

    ALTER TABLE table_name ADD COLUMN IF NOT EXISTS column_name INTEGER;
    
  • 1

    这基本上是来自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)');
    
  • 160

    你可以通过以下方式完成 .

    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;
    $$
    

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

    如果这些是来自外部源的随机字符串,我建议不要执行任何这些方法 . 无论你使用什么方法(作为查询执行的cleint端或服务器端动态字符串),它都会成为灾难的一个因素,因为它会打开你的SQL注入攻击 .

  • 0

    以下select查询将使用EXISTS()函数返回 true/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')
    

    并使用以下动态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
    $$
    

相关问题