Problem

While trying to use UNDROP TABLE to recover an accidentally dropped managed table, you notice a new table with the same name was created after the drop, preventing you from restoring the dropped table.

Cause

The UNDROP TABLE command cannot recover a dropped table if another table with the same name already exists in the target catalog and schema. 

Solution

Dropped managed tables are recoverable within a 7-day retention period by default. For tables dropped within seven days: 

First, rename the newly created table to eliminate the naming conflict.

ALTER TABLE <catalog>.<schema>.<current-table-name> RENAME TO <catalog>.<schema>.<temporary-table-name>;

For more information, refer to the ALTER TABLE (AWS | Azure | GCP) documentation. 
 

Then, recover the original dropped table. 

UNDROP TABLE <catalog>.<schema>.<original-table-name>;

For more information, refer to the UNDROP (AWS | Azure | GCP) documentation.
 

If you're unsure which table was dropped, you can use the command below to list recently dropped tables

SHOW TABLES DROPPED IN <catalog>.<schema>;

For details, refer to the SHOW TABLES DROPPED (AWS | Azure | GCP) documentation.