rename table in redshift

How to Rename Table in Redshift

Sometimes you may need to change table name in Redshift. In this article, we will look at how to rename table in Redshift using ALTER TABLE statement.


How to Rename Table in Redshift

It is very easy to rename table in Redshift using ALTER TABLE statement. Here is the syntax to change table name in Redshift.

ALTER TABLE old_table_name RENAME TO new_table_name;

In the above statement, you need to specify the old and new table names.

Also read : How to Drop Table in Redshift

For example, let us say you have a table sales(id, order_date, amount)

# select * from sales;
 id | order_date | amount
 ----+------------+--------
   1 | 2020-12-10 |    150
   2 | 2020-12-11 |    250
   3 | 2020-12-12 |    350

Here is the SQL statement to rename sales table to new_sales.

# alter table sales rename to new_sales;
 ALTER TABLE

# select * from new_sales;
  id | order_date | amount
 ----+------------+--------
   1 | 2020-12-10 |    150
   2 | 2020-12-11 |    250
   3 | 2020-12-12 |    350
 (3 rows)

# select * from sales;
 ERROR:  relation "sales" does not exist
 LINE 1: select * from sales;

Also read : How to Copy Table in Redshift

As you can see, once you rename table in Redshift, the old table name stops working. So you need to update stored procedures, views and functions that reference the old table with its new name.

Leave a Reply

Your email address will not be published. Required fields are marked *