Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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?

like image 988
RoyalTS Avatar asked Feb 04 '17 00:02

RoyalTS


2 Answers

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.

like image 86
John Rotenstein Avatar answered Oct 17 '22 15:10

John Rotenstein


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.

like image 4
FoxMulder900 Avatar answered Oct 17 '22 14:10

FoxMulder900