Как добавить столбец, если он не существует на PostgreSQL?

Вопрос прост. Как добавить столбец x в таблицу y, но только если столбец x не существует? Я нашел только решение здесь, как проверить, существует ли столбец.

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

Ответы

Ответ 1

Здесь коротко-сладкая версия с использованием инструкции "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;
$$

Вы не можете передать их как параметры, вам нужно будет выполнить замену переменных в строке на стороне клиента, но это автономный запрос, который выдает сообщение только в том случае, если столбец уже существует, добавляет, если он не работает 't и будет продолжать сбой при других ошибках (например, недопустимый тип данных).

Я не рекомендую делать ЛЮБОЙ из этих методов, если это случайные строки, поступающие из внешних источников. Независимо от того, какой метод вы используете (критические стороны или серверные динамические строки, выполняемые как запросы), это будет рецепт катастрофы, поскольку он откроет вам атаки SQL-инъекций.

Ответ 2

С Postgres 9.6 это можно сделать, используя опцию if not exists

ALTER TABLE table_name ADD COLUMN IF NOT EXISTS column_name INTEGER;

Ответ 3

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 при успехе, else FALSE (столбец уже существует).
Вызывает исключение для недопустимой таблицы или имени типа.

Почему еще одна версия?

  • Это можно сделать с помощью инструкции DO, но операторы DO ничего не могут вернуть. И если он для повторного использования, я бы создал функцию.

  • Я использую типы идентификаторов объектов regclass и regtype для _tbl и _type, которые: 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. Подробное объяснение:

  • Блоки, содержащие предложение EXCEPTION типа принятый в настоящее время ответ, значительно медленнее. Это, как правило, проще и быстрее. Документация:

Совет. Блок, содержащий предложение EXCEPTION, значительно больше дороже для входа и выхода, чем блок без него. Поэтому не делайте используйте EXCEPTION без необходимости.

Ответ 4

Следующий запрос select вернет true/false, используя EXISTS().

EXISTS():
Аргумент EXISTS - это произвольная инструкция SELECT или подзапрос. Подзапрос оценивается для определения того, возвращается ли он любые строки. Если он возвращает хотя бы одну строку, результатом EXISTS является "правда"; если подзапрос не возвращает строк, результатом EXISTS является "Ложь" 

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

Ответ 5

ниже функция проверяет столбец, если существует, возвращает соответствующее сообщение, иначе он добавит столбец в таблицу.

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

Ответ 6

Это в основном решение от sola, но немного почищено. Это достаточно различно, что я не просто хотел "улучшить" его решение (плюс, я вроде думаю, что это грубо).

Основное отличие заключается в том, что он использует формат EXECUTE. Я думаю, что это немного чище, но я считаю, что вы должны быть на PostgresSQL 9.1 или новее.

Это было проверено на 9.1 и работает. Примечание. Это приведет к возникновению ошибки, если имя схемы/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)');

Ответ 7

Может быть добавлен в скрипты миграции, вызывается функция и при необходимости удаляется.

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

Ответ 8

Вы можете сделать это следующим образом.

ALTER TABLE tableName drop column if exists columnName; 
ALTER TABLE tableName ADD COLUMN columnName character varying(8);

Таким образом, он удалит столбец, если он уже существует. Затем добавьте столбец в определенную таблицу.

Ответ 9

В моем случае, из-за того, как он был создан, нашим сценариям миграции немного сложно прорезать разные схемы.

Чтобы обойти это, мы использовали исключение, которое просто перехватывало и игнорировало ошибку. Это также имело приятный побочный эффект - на него было намного проще смотреть.

Однако следует опасаться, что другие решения имеют свои преимущества, которые, вероятно, перевешивают это решение:

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

Ответ 10

Просто проверьте, вернул ли запрос имя_столбца.

Если нет, выполните что-то вроде этого:

ALTER TABLE x ADD COLUMN y int;

Где вы помещаете что-то полезное для "x" и "y" и, конечно, подходящий тип данных, где я использовал int.