Redshift: add column if not exists

Question

The following works in Postgres 9.6 but not in Redshift:

ALTER TABLE stats
    ADD COLUMN IF NOT EXISTS panel_exit timestamp;

Can the same functionality be achieved in Redshift?

Swale

Answer 1

There is no Amazon Redshift command equivalent to ADD COLUMN IF NOT EXISTS.

See: ALTER TABLE documentation

To replicate this functionality, your application would first need to query the table metadata and then make the decision whether to issue the ADD COLUMN command.

Negatron

Answer 2

John's answer set me in the right direction, here is the command I found best worked in Redshift to check for a column's existence.

SELECT EXISTS(
    SELECT * FROM pg_table_def
    WHERE schemaname = '<my_schema_name>'
    and tablename = '<my_table_name>'
    and "column" = '<my_column_name>'
);

Note the double quotes around "column" are required since column is also a keyword.

Additionally, if the table you are interested in is not in the public schema. You may need to first modify your search path so the results are returned as expected:

set SEARCH_PATH to <schema_name>;

See the PG_TABLE_DEF AWS Docs for more details.

Duran

Answer 3

After much trial and error, this is one way in Redshift to add a new column if it does not exist.

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');

Other examples here:

• How to replicate IF NOT EXISTS in redshift

Homograph