Jak dodać kolumnę, jeśli nie istnieje w PostgreSQL?

Z Postgres 9.6 można to zrobić za pomocą opcji if not exists

ALTER TABLE table_name ADD COLUMN IF NOT EXISTS column_name INTEGER;

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;

Wywołanie:

SELECT f_add_col('public.kat', 'pfad1', 'int');

Zwraca {[3] } Po sukcesie, else FALSE (kolumna już istnieje).
Wyświetla wyjątek dla nieprawidłowej tabeli lub nazwy typu.

Dlaczego inna wersja?

• Można to zrobić za pomocą DO, ale DO nie może niczego zwrócić. A jeśli jest do wielokrotnego użytku, stworzyłbym funkcję.

• Używam typów identyfikatorów obiektów regclass i regtype dla _tbl i _type które A) uniemożliwiają SQL wtrysku i b) sprawdza ważność obu natychmiast (Najtańszy możliwy sposób). Nazwa kolumny {[11] } musi być jeszcze oczyszczona dla EXECUTE z quote_ident(). Więcej wyjaśnień w tej pokrewnej odpowiedzi:

  • Nazwa tabeli jako parametr funkcji PostgreSQL

• format() wymaga Postgres 9.1+. Dla starszych wersji konkatenować ręcznie:

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

• Możesz zakwalifikować nazwę tabeli, ale nie musisz.
  Możesz podwójne zacytowanie identyfikatorów w wywołaniu funkcji, aby zachować słowa wielbłąda i słowa zarezerwowane (ale i tak nie powinieneś ich używać).

• Zapytuję pg_catalog zamiast information_schema. Szczegółowe wyjaśnienie:

  • Jak sprawdzić, czy tabela istnieje w danym schemacie

• Bloki zawierające EXCEPTION klauzulę jak aktualnie akceptowana odpowiedź są znacznie wolniejsze. Jest to na ogół prostsze i szybsze. The dokumentacja:

Wskazówka: blok zawierający klauzulę EXCEPTION jest znacznie bardziej droga do wejścia i wyjścia niż blok bez niego. Dlatego nie użyj EXCEPTION bez potrzeby.

Po wybraniu zapytania zwróci true/false, używając EXISTS() funkcja.

Istnieje():
argumentem EXISTS jest dowolna instrukcja SELECT, lub subquery. Zapytanie podrzędne jest oceniane w celu określenia, czy zwraca dowolne rzędy. Jeśli zwraca co najmniej jeden wiersz, wynikiem EXISTS jest "true"; jeśli zapytanie podrzędne nie zwraca wierszy, wynikiem EXISTS jest "false"

SELECT EXISTS(
SELECT column_name 
FROM information_schema.columns 
WHERE table_schema='public' 
  and table_name='x' 
  and column_name='y')

I użyj następującego dynamicznego polecenia sql, aby zmienić swoje Tabela

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

Poniższa funkcja sprawdzi kolumnę jeśli istnieje zwróć odpowiedni komunikat w przeciwnym razie doda kolumnę do tabeli.

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

To jest w zasadzie rozwiązanie z sola, ale po prostu posprzątane trochę. Jest na tyle inny, że nie chciałem tylko "ulepszyć" jego rozwiązanie (poza tym uważam, że to niegrzeczne).

Główna różnica polega na tym, że używa formatu EXECUTE. Co moim zdaniem jest nieco czystsze, ale uważam, że oznacza to, że musisz być na PostgresSQL 9.1 lub nowszym.

To zostało przetestowane na 9.1 i działa. Uwaga: spowoduje to błąd, jeśli schemat/nazwa tabeli / lub typ danych są nieprawidłowe. To może "naprawione", ale może być poprawne zachowanie w wielu przypadkach.

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

Użycie:

select add_column('public', 'foo', 'bar', 'varchar(30)');

Możesz to zrobić postępując zgodnie ze sposobem.

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

Więc to upuści kolumnę, jeśli już istnieje. A następnie dodaj kolumnę do konkretnej tabeli.

Można dodać do skryptów migracyjnych wywoływać funkcję i upuszczać po zakończeniu.

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

Po prostu sprawdź, czy zapytanie zwróciło nazwę kolumny.

Jeśli nie, wykonaj coś takiego:

ALTER TABLE x ADD COLUMN y int;

Gdzie umieściłeś coś użytecznego dla ' x ' i ' y ' i oczywiście odpowiedni typ danych, gdzie użyłem int.