How to add column if not exists on PostgreSQL

Question

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

User · Answer

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

User · Answer

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

User · Answer

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  quot true quot   if the subquery returns no rows  the result of EXISTS is  quot false quot   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

User · Answer

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

User · Answer

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

User · Answer

Here s a short-and-sweet version using the  quot DO quot  statement  DO         BEGIN         BEGIN             ALTER TABLE  lt table name gt  ADD COLUMN  lt column name gt   lt column type gt           EXCEPTION             WHEN duplicate column THEN RAISE NOTICE  column  lt column name gt  already exists in  lt table name gt             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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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  lt tablename gt  add if not exists  lt columnname gt   lt columntype gt

User · Answer

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

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

Examples related to postgresql

Examples related to postgresql-9.1