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

Solution 1:

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;

Solution 2:

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.

Solution 3:

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.