Amazon Redshift create drop table if exists

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.

References:
http://docs.aws.amazon.com/redshift/latest/dg/r_DROP_TABLE.html
http://docs.aws.amazon.com/redshift/latest/dg/r_CREATE_TABLE_NEW.html
https://forums.aws.amazon.com/ann.jspa?annID=2668




This entry was posted in Amazon Redshift, Redshift Database Development by Khorshed. Bookmark the permalink.

About Khorshed

Khorshed has 12 years of cumulative professional experience in areas of database administration, development, Business Intelligent, system appreciation, requirement analysis and software development (using .net technologies). Achieved MCITP in SQL Server in both SQL Server development and administration. He has vast knowledge in SQL Server, SSIS, SSRS, SSAS. He is also proficient in Amazon cloud services like Redshift, Data Pipeline, SNS, DynamoDB, EMR and many more.

2 thoughts on “Amazon Redshift create drop table if exists

Leave a Reply

Your email address will not be published. Required fields are marked *

20 + five =