Rename a table in Amazon Redshift

Question

I've been trying to rename a table from "fund performance" to fund_performance in SQLWorkbench for a Redshift database. Commands I have tried are:

alter table schemaname."fund performance"
rename to fund_performance; 

I received a message that the command executed successfully, and yet the table name did not change. I then tried copying the table to rename it that way. I used

#CREATE TABLE fund_performance LIKE "schema_name.fund performance";
CREATE TABLE fund_performance AS SELECT * FROM schema_name."fund performance";

In both these cases I also received a message that the statements executed successfully, but nothing changed. Does anyone have any ideas?

Answer 1

Use following it may work out for you

SELECT * into schema_name.fund_performance FROM schema_name.[fund performance]

It will copy the data by creating new table as fund_performance but it won't create any constraints and Identity's

To Rename specific table without disturbing existing constraints

EXEC sp_rename 'schema_name.[fund performance]', 'schema_name.fund_performance';