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

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

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.