Redshift: Add Column If Not Exists

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?

Solution 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.

Solution 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.

SELECTEXISTS(
    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.