How to add column if not exists on PostgreSQL?

Question

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;

Here's a short-and-sweet version using the "DO" statement:

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

You can't pass these as parameters, you'll need to do variable substitution in the string on the client side, but this is a self contained query that only emits a message if the column already exists, adds if it doesn't and will continue to fail on other errors (like an invalid data type).

I don't recommend doing ANY of these methods if these are random strings coming from external sources. No matter what method you use (client-side or server-side dynamic strings executed as queries), it would be a recipe for disaster as it opens you to SQL injection attacks.

Postgres 9.6 added ALTER TABLE tbl ADD COLUMN IF NOT EXISTS column_name.
So this is mostly outdated now. You might use it in older versions, or a variation to check for more than just the column name.

---

CREATE OR REPLACE function f_add_col(_tbl regclass, _col  text, _type regtype)
  RETURNS bool
  LANGUAGE plpgsql AS
$func$
BEGIN
   IF EXISTS (SELECT 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$;

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(). See:

  • 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 are substantially slower.
  This is simpler and faster. The manual:

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.

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>

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