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.

2 REPLIES
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;

Teradata Employee

Re: Renaming a Table in Aster

FYI,

 

I think the transaction tric is not necessary. At least in recent version (I check v 7.0.2).

Aster's rename table prohibits target shcema and always renames it to same schema.

 

eg)

 

show search_path;
-- "public"

create table sandbox.t1(c1 int)distribute by replication;
alter table rename sandbox.t1 to t2;

select * from sandbox.t2;
-- success

select * from public.t2;
-- fail (public.t2 does not exist)