How to replicate IF NOT EXISTS in redshift

Question

I am trying to replicate a functionality from SQL Server into redshift where I have to ignore column if the column exists, otherwise add it into the table.

I have come across these posts, however couldn't find a proper solution from them:

1. Redshift Alter table if not exists
2. Redshift: add column if not exists
3. Workaround in Redshift for "ADD COLUMN IF NOT EXISTS"

I am able to get a TRUE or FALSE for columns that I want to check. But I don't know how to ALTER the table to add or remove one.

These are some of my attempts:

IF (SELECT EXISTS(SELECT * FROM pg_catalog.pg_table_def
    WHERE schemaname = 'my_schema'
     AND tablename = 'my_table'
     AND "column" = 'my_new_column'
)) <> TRUE
THEN
   ALTER TABLE my_table
   ADD COLUMN my_new_column varchar
END IF;

CREATE OR REPLACE PROCEDURE if_else()
LANGUAGE plpgsql
AS $$
BEGIN
IF (SELECT EXISTS(SELECT * FROM pg_catalog.pg_table_def
    WHERE schemaname = 'my_schema'
     AND tablename = 'my_table'
     AND "column" = 'my_new_column'
)) <> TRUE
THEN
   ALTER TABLE my_table
   ADD COLUMN my_new_column varchar
END IF;
END;
$$
;

CALL if_else();

A few more failed attempts:

CREATE OR REPLACE PROCEDURE alter_my_table()
AS $$
BEGIN
   ALTER TABLE my_table
   ADD COLUMN my_new_column varchar
END;
$$
LANGUAGE plpgsql
;

SELECT 
   CASE WHEN COUNT(*) THEN 'warning: column exists already.'
   ELSE CALL alter_my_table();
   END
FROM pg_catalog.pg_table_def
     WHERE schemaname = 'my_schema'
     AND tablename = 'my_table'
     AND "column" = 'my_new_column'

Thank you for your time.

Nummular

IF NOT EXISTS is a bad idea. If the columns already exists with a different definition, you'll later get all kinds of inexplicable obscure app errors that's going to take effort to shed light on. Sow winds and harvest storms ... – Swift

Hi @TheImpaler. Thank you for the suggestion. But really, I am doing mostly for learning process. And I most likely going to require it and if it fails in the future, I won't be blamed :( – Nummular

Answer 1

I have done something like this, which works fairly well.

CREATE OR REPLACE PROCEDURE add_table_column(s_name varchar, t_name varchar, c_name varchar, c_type varchar)
LANGUAGE plpgsql
AS $$
BEGIN
IF (SELECT count(1) FROM pg_catalog.pg_table_def
    WHERE schemaname = s_name
     AND tablename = t_name
     AND "column" = c_name
) = 0
THEN
   execute 'ALTER TABLE '||s_name||'.'||t_name||' ADD COLUMN '||c_name||' '||c_type;
END IF;
END;
$$
;

create table public.tst (col1 numeric);

call add_table_column('public','tst','col2','numeric');

Nummular