[postgresql] How to add column if not exists on PostgreSQL?

Question is simple. How to add column x to table y, but only when x column doesn't exist ? I found only solution here how to check if column exists.

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

In my case, for how it was created reason it is a bit difficult for our migration scripts to cut across different schemas.

To work around this we used an exception that just caught and ignored the error. This also had the nice side effect of being a lot easier to look at.

However, be wary that the other solutions have their own advantages that probably outweigh this solution:

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

Simply check if the query returned a column_name.

If not, execute something like this:

ALTER TABLE x ADD COLUMN y int;

Where you put something useful for 'x' and 'y' and of course a suitable datatype where I used int.

With Postgres 9.6 this can be done using the option if not exists

ALTER TABLE table_name ADD COLUMN IF NOT EXISTS column_name INTEGER;

Can be added to migration scripts invoke function and drop when done.

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

Following select query will return true/false, using EXISTS() function.

EXISTS():
The argument of EXISTS is an arbitrary SELECT statement, or subquery. The subquery is evaluated to determine whether it returns any rows. If it returns at least one row, the result of EXISTS is "true"; if the subquery returns no rows, the result of EXISTS is "false"

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

and use the following dynamic SQL statement to alter your table

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

For those who use Postgre 9.5+(I believe most of you do), there is a quite simple and clean solution

ALTER TABLE if exists <tablename> add if not exists <columnname> <columntype>

You can do it by following way.

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

So it will drop the column if it is already exists. And then add the column to particular table.

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;

Call:

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

Returns TRUE on success, else FALSE (column already exists).
Raises an exception for invalid table or type name.

Why another version?

• This could be done with a DO statement, but DO statements cannot return anything. And if it's for repeated use, I would create a function.

• I use the object identifier types regclass and regtype for _tbl and _type which a) prevents SQL injection and b) checks validity of both immediately (cheapest possible way). The column name _col has still to be sanitized for EXECUTE with quote_ident(). More explanation in this related answer:

  • Table name as a PostgreSQL function parameter

• format() requires Postgres 9.1+. For older versions concatenate manually:

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

• You can schema-qualify your table name, but you don't have to.
  You can double-quote the identifiers in the function call to preserve camel-case and reserved words (but you shouldn't use any of this anyway).

• I query pg_catalog instead of the information_schema. Detailed explanation:

  • How to check if a table exists in a given schema

• Blocks containing an EXCEPTION clause like the currently accepted answer are substantially slower. This is generally simpler and faster. The documentation:

Tip: A block containing an EXCEPTION clause is significantly more expensive to enter and exit than a block without one. Therefore, don't use EXCEPTION without need.

This is basically the solution from sola, but just cleaned up a bit. It's different enough that I didn't just want to "improve" his solution (plus, I sort of think that's rude).

Main difference is that it uses the EXECUTE format. Which I think is a bit cleaner, but I believe means that you must be on PostgresSQL 9.1 or newer.

This has been tested on 9.1 and works. Note: It will raise an error if the schema/table_name/or data_type are invalid. That could "fixed", but might be the correct behavior in many cases.

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

usage:

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

the below function will check the column if exist return appropriate message else it will add the column to the table.

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