Redshift Alter table if not exists

Question

I'm trying add a new field in a redshift table. But I want to add only if this field doesn't exists. I tried wrapping it with IF NOT EXISTS. But I got following error: Amazon](500310) Invalid operation: syntax error at or near "IF" Position: 5;

BEGIN
  IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_schema = 'schema_name' and table_name='table_name' and column_name='new_field') THEN 
    ALTER TABLE schema_name.table_name
    ADD new_field INT;
  END IF;

COMMIT;

I'm not sure if I'm correctly using "IF NOT EXISTS" statement inside the BEGIN block. Can someone please help me?

Thanks in advance!

Yauheni Khvainitski Yauheni Khvainitski · Accepted Answer · 2020-12-02T07:33:43

It could be better to handle it using EXCEPTION

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;

Redshift Alter table if not exists

1 Answers