drop table not working because illegal characters in name

Question

I have created a table in Redshift accidentally putting a period in the name. I intended to create a table called "clusters" in my schema called "client3". However instead of the command  
  
CREATE TABLE client3.clusters  
  
I used the command  
  
CREATE TABLE "client3.clusters"  
  
and now I have a table called "client3.clusters" in my public schema.  
  
If I click on the 3 dots to the right of the table name and choose Drop Table, the query field gets populated with this:  
  
drop table public.client3.clusters;  
  
I get this error message:  
  
ERROR: cross-database reference to database "public" is not supported.  
  
If I try to use quotes, as in  
  
drop table "public.client3.clusters";  
  
I get the error message  
  
ERROR: Table "public.client3.clusters" does not exist  
  
even though I see this table in the list under public.   
  
I would appreciate some advice on how to drop this table. Thanks!

Accepted Answer

You need to double quote the table name separately:

```
dev=# CREATE TABLE "client3.clusters" AS SELECT 1;
SELECT
dev=# DROP TABLE "public.client3.clusters";
ERROR:  Table "public.client3.clusters" does not exist
dev=# DROP TABLE public."client3.clusters";
DROP TABLE
```

Answer

Thank you! Worked perfectly.

drop table not working because illegal characters in name

Relevanter Inhalt