在Redshift中创建后如何更改表架构? [英] How to change table schema after created in Redshift?

问题描述

Postgre支持以下操作:

Postgre supports this operation as below:

ALTER TABLE name
    SET SCHEMA new_schema

该操作在Redshift中不起作用.有什么办法吗?

The operation won't work in Redshift. Is there any way to do that?

我试图更新pg_class来为表设置relnamespace(schema id)，这需要超级用户帐户，并且在pg_shadow表中usecatupd是true.但是我得到了权限被拒绝的错误.唯一可以修改pg系统表的帐户是rdsdb.

I tried to update pg_class to set relnamespace(schema id) for the table, which needs superuser account and usecatupd is true in pg_shadow table. But I got permission denied error. The only account who can modify pg system table is rdsdb.

server=# select * from pg_user;
 usename   | usesysid | usecreatedb | usesuper | usecatupd |  passwd  | valuntil |            useconfig             
------------+----------+-------------+----------+-----------+----------+----------+----------------------------------
 rdsdb      |        1 | t           | t        | t         | ******** |          | 
 myuser     |      100 | t           | t        | f         | ******** |          | 

那么真的红移对此没有许可吗?

So really redshift gives no permission for that?

推荐答案

执行此操作的最佳方法是使用所需的架构创建一个新表，然后执行INSERT .... SELECT操作.旧表.

The best way to do that is to create a new table with the desired schema, and after that do an INSERT .... SELECT with the data from the old table.

然后删除当前表，并使用ALTER TABLE重命名新表.

Then drop your current table and rename the new one with ALTER TABLE.

这篇关于在Redshift中创建后如何更改表架构?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！