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