As a part of maintenance and releasing new features, “Redshift create drop table if exists” option is now available in the database after Oct 16, 2014. Previously, it was bit cumbersome to check the existence of a table prior to create or drop it.
Previous approach of Redshift create drop table if exists
Querying in the Redshift catalog or information schema table, was the only way to know if any table exists or not. For example, in past, in order to check the existence of a table the following extra query need to be issued in Redshift:
SELECT TOP 1 tablename FROM pg_table_def t WHERE t.tablename = 'test';
Based on the result of the above query, new tables are created or existing tables are dropped as per developer requirement.
Current approach of Redshift create drop table if exists
Amazon has realized this essential user need and addressed this issue in their most latest update. Table existence can be checked now along with CREATE TABLE or DROP table statements using new IF EXISTS keyword. The following statement creates a table if it does not exist.
CREATE TABLE IF NOT EXISTS Test (ID INT);
Similarly, IF EXISTS can be used along with DROP, VIEW, SCHEMA and USER DDL statement as well. Have a look on the query below:
DROP TABLE IF EXISTS test;
The query above will delete the table named “test” if this is present in the database.I hope this information will be helpful to you.