SQL Error on Amazon Redshift Database during Drop Table Command Execution

Recently a SQL developer created a database table on Amazon Redshift cluster with a name including double quotes and during SQL DROP TABLE command execution experienced a SQL error because of syntax error. In this short Amazon Redshift SQL tutorial for database developers, I want to show how to get rid of double quotes within table names in DROP TABLE SQL command.

How can you drop a database table on Redshift named businessdev."marketprediction" in public schema?
Pay attention to the table name. It contains (") double quotes in its name.

problematic Amazon Redshift database table name


DROP TABLE public."businessdev."marketprediction"";

SQL Drop Table Command

On SQL Workbench/J (a SQL database management tool) when I click on the table name and select Drop to execute the drop table command using GUI

run drop table command on SQL Workbench

Following SQL error occurs and raised by Amazon Redshift cluster

An error occurred when executing the SQL command:
DROP TABLE public."businessdev."marketprediction""
[Amazon](500310) Invalid operation: syntax error at or near "marketprediction"
Position: 33; [SQL State=42601, DB Errorcode=500310]

SQL Workbench error during Drop Table command execution on Redshift database

The correct syntax for drop table command with such complex table names is as follows on Redshift database


DROP TABLE "businessdev.""marketprediction""";

Delete Table in SQL

Just remove the "public" schema name from the command.
By default if we do not provide the schema name SQL assumes it as "public" schema

Then for the double quotes " in the name of the table, replace each double quote with two double quotes
So, table name businessdev."marketprediction" should be escaped as businessdev.""marketprediction""
And we add an enclosing double quotes, at the begining and at the end of the table name