easy, elegant and effective code…

Sometimes, it is required to write a SQL script such that multiple executions on the script should not fail. To address such requirement, generally you have following options –

1. You can execute the SQL statements conditionally (with some check)
e.g. –

DROP TABLE IF EXISTS <table name> CASCADE;

INSERT INTO <table> (<pk_column>, <column_2>) SELECT 'pk_column_value', 'column_2_value' WHERE NOT EXISTS (SELECT 'Y' FROM <table> WHERE <pk_column> = 'some value' );

2. You can use the options provided in the database itself
e.g. –

CREATE OR REPLACE FUNCTION <function name>

We had the similar requirement. We wanted to create a script which should upgrade our data model from version x to version x+1 and it should not fail even if the script is executed multiple time. We could not use any of the above option in case of adding a column to an existing table.

PostgreSQL provide an option of checking if the column already exists or not while dropping the column. However, it does not provide such straight forward way while adding a column to check if the column is already there in the table or not. Also, we did not want to drop the column first and then add the column because in that case we might lose the data (if there is some).

Solution for the problem is to query the tables where Postgres stores the schema metadata. For checking if a column exists or not in a particular table, you need to execute a SELECT query on the JOIN of two tables – PG_ATTRIBUTE and PG_CLASS, which stores the information about columns and tables respectively (Query is highlighed in the code given below).

CREATE OR REPLACE FUNCTION test.upgrade_schema()
RETURNS void AS
$BODY$
DECLARE var_column_exists VARCHAR(32);
BEGIN
	SELECT A.ATTNAME INTO VAR_COLUMN_EXISTS FROM PG_ATTRIBUTE A, PG_CLASS C
	WHERE A.ATTRELID = C.OID AND A.ATTNAME = 'column_to_add' AND C.RELNAME = 'table_to_alter';

	IF var_column_exists IS NULL THEN
		ALTER TABLE test.table_to_alter ADD COLUMN  column_to_add varchar(256) DEFAULT NULL;
	END IF;
END;
$BODY$
LANGUAGE plpgsql
;

References

http://www.postgresql.org/docs/8.0/static/plpgsql.html
http://www.postgresql.org/docs/8.1/static/catalog-pg-class.html
http://www.postgresql.org/docs/8.1/static/catalog-pg-attribute.html

Advertisements

Comments on: "PostgreSQL: Add Column If Not Exists" (2)

  1. When having pre 9.0 postgres database the if exists is not available for alter table statements.
    In case you want to drop a column only when it is there the following function might help:

    CREATE OR REPLACE FUNCTION drop_column_if_exists(text, text)
    RETURNS boolean AS
    $BODY$
    DECLARE colname ALIAS FOR $1;
    DECLARE tablename ALIAS FOR $2;
    DECLARE found boolean;
    BEGIN
    select count(*) into found from information_schema.columns where table_name = tablename and column_name = colname;
    IF found THEN
    EXECUTE ‘ALTER TABLE ‘|| tablename || ‘ DROP COLUMN ‘ || colname || ‘ CASCADE’;
    return true;
    END IF;
    return false;
    END;
    $BODY$
    LANGUAGE plpgsql
    ;

    You can do it in a similar way for other table manipulation operations.
    Also note that you can use the information_schema to find the needed metadata, which is a little bit mor ANSI SQL compliant ….

    • I have not validated your approach. However, I believe that it would help other (pre) postgres users in case they encountered the similar issue. Thanks for your comment 🙂

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: