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!

Starfish

Did you got any solution for this? – Toole

Answer 1

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;

Alliterative

We use such a solution in production at our project – Alliterative

Is this solution works in Redshift? seems not. – Toole

Answer 2

The Answer by Yauheni Khvainitski is not completely wrong. But you do have to use a SP and the only option Redshit has (at this point is to have "EXCEPTION WHEN OTHER"). An example:

CREATE OR REPLACE PROCEDURE change_column_to_big_int_TABLE_NAME_X(column_name varchar(200)) AS
$$
DECLARE
  new_column_name VARCHAR;
BEGIN
 SELECT INTO new_column_name (table_name)||'_new';
-- RAISE INFO 'new_table_name = % table_name = %',new_column_name, table_name;
 ALTER TABLE TABLE_NAME_X ADD COLUMN "(new_column_name)" bigint;
 EXCEPTION WHEN OTHERS
 THEN RAISE NOTICE 'column already exists on table';
END;
$$
LANGUAGE plpgsql;
    
CALL change_column_to_big_int_TABLE_NAME_X('COLUMN_Y');

Some links from AWS on:

• trapping errors: https://docs.aws.amazon.com/redshift/latest/dg/stored-procedure-trapping-errors.html
• CREATE PROCEDURE: https://docs.aws.amazon.com/redshift/latest/dg/r_CREATE_PROCEDURE.html

Also please notice that this is valid at this point in time. Redshift seems to be always evolving.

Nonessential

Answer 3

The issue I think is that AWS Redshift does not support the IF statement, but instead uses CASE statements. The CASE statements are very similar to IF the way they implement them. But I admit, I prefer the IF statements.

Doggy

This does not provide an answer to the question. Once you have sufficient reputation you will be able to comment on any post; instead, provide answers that don't require clarification from the asker. - From Review – Undrape

Answer 4

Uses dynamic sql. Be sure to adjust your schema - I'm using workdb. There's prob enhancements one can make to this.

CREATE OR REPLACE PROCEDURE workdb.add_column_if_not_exists(
    p_schema_name VARCHAR(256),
    p_table_name VARCHAR(256),
    p_column_name VARCHAR(256),
    p_column_data_type VARCHAR(256)
)
AS $$


BEGIN
    -- Check if the column exists
   RAISE NOTICE '***New Column Info***: %', p_schema_name;
   RAISE NOTICE 'p_schema: %', p_schema_name;
   RAISE NOTICE 'p_table_name: %', p_table_name;
   RAISE NOTICE 'p_column_name: %', p_column_name;
   RAISE NOTICE 'p_column_data_type: %', p_column_data_type;          
          
    IF ( select
        count(*)  cnt   
        from INFORMATION_SCHEMA.COLUMNS
        where TABLE_SCHEMA = p_SCHEMA_NAME
            and TABLE_NAME = p_table_name
            and column_name = p_column_name
    ) = 0 THEN

          RAISE NOTICE 'Adding new column';
          EXECUTE 'ALTER TABLE ' || p_SCHEMA_NAME || '.' || p_table_name || ' ADD COLUMN ' || p_COLUMN_NAME || ' ' || p_COLUMN_DATA_TYPE;
          RAISE NOTICE 'New column Added';
    ELSE 
          RAISE NOTICE 'Column exists already';
    END IF;
   
END;

$$ LANGUAGE plpgsql
;

Then you can call it like this:

CALL workdb.add_column_if_not_exists('workdb', 'test_table', 'new_col', 'int');

via: https://mcmap.net/q/979688/-redshift-add-column-if-not-exists

Okubo