How to add column if not exists on PostgreSQL?

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.

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.