Cómo agregar columna si no existe en PostgreSQL?

Con Postgres 9.6 esto se puede hacer usando la opción 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;

Llamada:

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

Devuelve TRUE en caso de éxito, de lo contrario FALSE (la columna ya existe).
Genera una excepción para un nombre de tipo o tabla no válido.

¿Por qué otra versión?

• Esto podría hacerse con una instrucción DO, pero las instrucciones DO no pueden devolver nada. Y si es para uso repetido, crearía una función.

• Utilizo los tipos de identificador de objeto regclass y regtype para _tbl y _type que a) impide SQL inyección y b) comprueba la validez de ambos inmediatamente (la forma más barata posible). El nombre de la columna _col todavía tiene que ser desinfectado para EXECUTE con quote_ident(). Más explicación en esta respuesta relacionada:

  • Nombre de la tabla como parámetro de función PostgreSQL

• format() requiere Postgres 9.1+. Para versiones anteriores concatenar manualmente:

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

• Puede calificar el nombre de la tabla de forma schema, pero no tiene que hacerlo.
  Puedes comilla dos veces los identificadores en la llamada a la función para preservar las palabras camel-case y reservadas (pero no debería usar nada de esto de todos modos).

• I query pg_catalog instead of the information_schema. Explicación detallada:

  • Cómo comprobar si existe una tabla en un esquema dado

• Los bloques que contienen una cláusula EXCEPTION como la respuesta actualmente aceptada son sustancialmente más lentos. Esto es generalmente más simple y más rápido. El documentación:

Consejo: Un bloque que contiene una cláusula EXCEPTION es significativamente más caro para entrar y salir de un bloque sin uno. Por lo tanto, no utilice EXCEPTION sin necesidad.

Después de seleccionar la consulta devolverá true/false, usando EXISTS() función.

EXISTE():
El argumento de EXISTS es una sentencia SELECT arbitraria, o subconsultas. La subconsulta se evalúa para determinar si devuelve cualquier fila. Si devuelve al menos una fila, el resultado de EXISTS es "true"; si la subconsulta no devuelve filas, el resultado de EXISTS es "false"

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

Y utilice la siguiente instrucción sql dinámica para alterar su tabla

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

La siguiente función verificará la columna si existe devolver el mensaje apropiado o agregará la columna a la tabla.

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

Esta es básicamente la solución de sola, pero solo limpiado un poco. Es lo suficientemente diferente que no solo quería "mejorar" su solución (además, creo que es grosero).

La principal diferencia es que utiliza el formato EXECUTE. Lo que creo que es un poco más limpio, pero creo que significa que debe estar en PostgresSQL 9.1 o posterior.

Esto ha sido probado en 9.1 y funciona. Nota: Generará un error si el esquema/table_name/o data_type no son válidos. Eso podría "fijo", pero podría ser el comportamiento correcto en muchos casos.

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

Uso:

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

Puede hacerlo siguiendo el camino.

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

Por lo que dejará caer la columna si ya existe. Y luego agregue la columna a una tabla particular.

Se puede agregar a los scripts de migración invocar la función y soltar cuando termine.

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

Simplemente compruebe si la consulta devolvió un column_name.

Si no, ejecuta algo como esto:

ALTER TABLE x ADD COLUMN y int;

Donde pones algo útil para 'x' e 'y' y por supuesto un tipo de datos adecuado donde utilicé int.