SQL Server administration and T-SQL development, Web Programming with ASP.NET, HTML5 and Javascript, Windows Phone 8 app development, SAP Smartforms and ABAP Programming, Windows 7, Visual Studio and MS Office software
Development resources, articles, tutorials, code samples, tools and downloads for AWS Amazon Web Services, Redshift, AWS Lambda Functions, S3 Buckets, VPC, EC2, IAM

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"";

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""";

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


Copyright © 2004 - 2021 Eralper YILMAZ. All rights reserved.