Renaming a Table in Aster

Aster
Enthusiast

Renaming a Table in Aster

How to rename a table in Aster?

if you are in a schema = ABC, but the search path schema does not have ABC, then it might be challenging to rename the table in schema ABC.

1 REPLY
Enthusiast

Re: Renaming a Table in Aster

Amateurs like me get a failure error when the following syntax is submitted.

FAILS

alter table ABC.rename_test rename to ABC.rename_test_2;  --Fails

But when you take a deep dive into this, you might see two ways to do it

WORKS

method (i): 

alter table ABC.rename_test_1 rename to rename_test; --Works : Do NOT specify schema name in the destined table name 

method (ii): set search_path

begin;

set search_path='ABC';

show search_path; --This should give ABC as result

alter table rename_test_1 rename to rename_test; --Works 

end;