0

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?

John Rotenstein
  • 241,921
  • 22
  • 380
  • 470
Walt
  • 19
  • 4
  • check in other default schemas in there it may be got created – Upender Reddy Jun 29 '22 at 09:38
  • It could be that SQLWorkbench is caching the name of the table. After you use the `ALTER TABLE` command, are you able to `SELECT COUNT(*) FROM fund_performance`? Also, check whether SQLWorkbench has Auto-Commit enabled (turn it on). This shouldn't impact DDL statements, but it's worth checking. – John Rotenstein Jun 29 '22 at 09:45

1 Answers1

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';
Upender Reddy
  • 568
  • 3
  • 8