How to mimic "IF NOT EXISTS" when adding a new column on postgresql 9.3?-CodePudding

On postgresql 9.3, there is no IF NOT EXISTS directive for adding a new column on a table. So I obviously get a syntax error, by trying to use it:

ALTER TABLE rooms ADD COLUMN IF NOT EXISTS color VARCHAR(6);


ERROR:  syntax error at or near "NOT"
LINE 1: ALTER TABLE rooms ADD COLUMN IF NOT EXISTS color VARCHAR(6);

As I don't have the possibility to upgrade to a more up-to-date version, what would be the alternate way of doing this ?

CodePudding user response：

One possibility:

\d animals
                      Table "public.animals"
 Column |          Type          | Collation | Nullable | Default 
-------- ------------------------ ----------- ---------- ---------
 id     | integer                |           | not null | 
 cond   | character varying(200) |           | not null | 
 animal | character varying(200) |           | not null | 
Indexes:
    "animals_pkey" PRIMARY KEY, btree (id)

DO
$$
BEGIN
    perform * from pg_attribute where attrelid = 'animals'::regclass and attname = 'cond';
    IF FOUND THEN
        RAISE NOTICE 'Column exists';
    ELSE 
        ALTER TABLE animals ADD COLUMN cond varchar;
    END IF;
END$$;
NOTICE:  Column exists
DO

DO
$$
BEGIN
    perform * from pg_attribute where attrelid = 'animals'::regclass and attname = 'new_col';
    IF FOUND THEN
        RAISE NOTICE 'Column exists';
    ELSE 
        ALTER TABLE animals ADD COLUMN new_col varchar;
    END IF;
END$$;
DO

\d animals
                      Table "public.animals"
 Column  |          Type          | Collation | Nullable | Default 
--------- ------------------------ ----------- ---------- ---------
 id      | integer                |           | not null | 
 cond    | character varying(200) |           | not null | 
 animal  | character varying(200) |           | not null | 
 new_col | character varying      |           |          | 
Indexes:
    "animals_pkey" PRIMARY KEY, btree (id)

This is presented as an anonymous function(DO), but it could be made a regular function e.g. add_column(table_name varchar, column_name varchar)

UPDATE

A regular function:

CREATE OR REPLACE FUNCTION public.add_column(table_name character varying, column_name character varying, col_type character varying)
 RETURNS void
 LANGUAGE plpgsql
AS $function$
BEGIN
    perform * from pg_attribute where attrelid = table_name::regclass and attname = quote_ident(column_name);
    IF FOUND THEN
        RAISE NOTICE 'Column exists';
    ELSE
        EXECUTE 'ALTER TABLE animals ADD COLUMN  '|| quote_ident(column_name) || ' ' ||col_type;
    END IF;
END;
$function$
;
select add_column('animals', 'int_col', 'integer');
 add_column 
------------
 
\d animals
                      Table "public.animals"
 Column  |          Type          | Collation | Nullable | Default 
--------- ------------------------ ----------- ---------- ---------
 id      | integer                |           | not null | 
 cond    | character varying(200) |           | not null | 
 animal  | character varying(200) |           | not null | 
 new_col | character varying      |           |          | 
 int_col | integer                |           |