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

вопрос простой. Как добавить столбец x в таблице y, но только тогда, когда

1113   9  

9 ответов:

вот короткая и сладкая версия, использующая оператор "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-side или серверные динамические строки, выполняемые как запросы), это будет рецепт катастрофы, поскольку он открывает вам атаки SQL-инъекции.

95  
2012-09-26 21:59:08

С Postgres 9.6 Это можно сделать с помощью опции if not exists

ALTER TABLE table_name ADD COLUMN IF NOT EXISTS column_name INTEGER;
142  
2017-08-01 18:22:51 
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 операторы не могут ничего вернуть. И если это для повторного использования, я бы создал функцию.

  • я использую идентификатор объекта типы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;

  • вы можете определить схему вашего имени таблицы, но вам это не нужно.
    Вы можете дважды процитировать идентификаторы в вызове функции, чтобы сохранить Camel-case и зарезервированные слова (но вы все равно не должны использовать это).

  • запрос pg_catalog вместо information_schema. Подробное объяснение:

  • блоки, содержащие EXCEPTION пункт как в настоящее время принимаются ответ существенно медленнее. Это, как правило, проще и быстрее. документация:

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

19  
2017-05-23 14:47:18

следующий запрос select вернет true/false, используя EXISTS()():
аргумент EXISTS является произвольным оператором SELECT, или подзапрос. Подзапрос вычисляется, чтобы определить, возвращает ли он все строки. Если он возвращает хотя бы одну строку, результатом EXISTS является "true"; если подзапрос не возвращает строк, результатом 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
$$
16  
2016-06-30 10:39:37

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

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;
$$
1  
2012-09-26 13:28:45

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

основное отличие заключается в том, что он использует формат выполнения. Что я думаю, немного чище, но я считаю, что это означает, что вы должны быть на 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)');
1  
2012-09-26 17:23:31

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

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

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

1  
2016-03-29 07:51:47

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

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  
2014-03-12 15:57:14

просто проверьте, если запрос вернул column_name.

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

ALTER TABLE x ADD COLUMN y int;

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

-4  
2012-09-26 11:25:35

Comments

    Ничего не найдено.
Click here to cancel reply.