How to add a column if it does not exist on PostgreSQL?

Question

The question is simple. How to add column x to table y , but only if column x does not exist? I only found a solution here , how to check if a column exists.

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

Answer 1

Here's a short-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 cannot pass them as parameters, you will need to perform the replacement of variables in the row on the client side, but this is a standalone request that gives a message only if the column already exists, adds if it doesn’t work and will continue to fail for other errors (for example, an invalid data type).

I do not recommend doing ANY of these methods if these are random strings coming from external sources. Regardless of which method you use (critical sides or server-side dynamic strings that execute as queries), this will be a recipe for disaster, as it will open SQL injection attacks for you.

Answer 2

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

 ALTER TABLE table_name ADD COLUMN IF NOT EXISTS column_name INTEGER; 

Answer 3

 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).
Throws an exception for an invalid table or type name.

Why another version?

• This can be done using the DO statement, but DO statements cannot return anything. And if it is for reuse, I would create a function.

• I use regclass and regtype object identifier types for _tbl and _type , which: a) prevent SQL injection and b) validate as soon as possible (the cheapest way). The column name _col should still be sanitized for EXECUTE using quote_ident() . More explanation in this related answer:

  • Table name as parameter of PostgreSQL function

• format() requires Postgres 9.1+. For older versions, manual concatenation:

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

• You can schematize your table name, but you don't need to.
  You can specify identifiers twice in a function call to store camel and reserved words (but you shouldn't use them anyway).

• I will pg_catalog instead of information_schema . Detailed explanation:

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

• Blocks containing an EXCEPTION clause of the type currently accepted are significantly slower. It is usually simpler and faster. Documentation:

Tip. A block containing an EXCEPTION clause is significantly more expensive to enter and exit than a block without it. Therefore, do not use EXCEPTION unnecessarily.

Answer 4

The following select query will return true/false using EXISTS() .

EXISTS () :
The EXISTS argument is an arbitrary SELECT statement or subquery. A subquery is evaluated to determine if it returns any rows. If it returns at least one row, the result of EXISTS is true; if the subquery does not return 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 query to modify the 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 $$ 

Answer 5

below the function checks the column, if it exists, returns the corresponding message, otherwise 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; $$ 

Answer 6

This is basically a sola solution, but a little cleaned. It is quite different that I didn’t just want to “improve” his solution (plus, I kind of think that is rude).

The main difference is that it uses the EXECUTE format. I think this is a little cleaner, but I believe you should be on PostgresSQL 9.1 or later.

It has been tested on 9.1 and works. Note. This will result in an error if the schema name / table_name / or data_type is invalid. This may be “fixed,” but in many cases it may be the correct behavior.

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

using:

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

Answer 7

It can be added to migration scripts, a function is called and deleted if necessary.

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

Answer 8

You can do it as follows.

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

This way it will delete the column if it already exists. Then add the column to the specific table.

Answer 9

In my case, because of how it was created, it is a little difficult for our migration scenarios to cut through different schemes.

To get around this, we used an exception that simply caught and ignored the error. It also had a pleasant side effect - it was much easier to look at.

However, it should be feared that other solutions have 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 $$; 

Answer 10

Just check if the query returned column_name.

If not, do something like this:

 ALTER TABLE x ADD COLUMN y int; 

Where do you put something useful for "x" and "y" and, of course, a suitable data type, where I used int.