如何在Redshift中创建表后更改表结构？

Question

PostgreSQL支持以下操作：

ALTER TABLE name
    SET SCHEMA new_schema

这个操作在 Redshift 中不起作用。有没有其他方法可以实现？

我尝试更新 pg_class 表以设置表的 relnamespace（模式 ID），但需要超级用户帐户和 pg_shadow 表中 usecatupd 为 true。但是我收到了权限被拒绝的错误。唯一可以修改 pg 系统表的帐户是 rdsdb。

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

那么，Redshift真的不允许那样做吗？

- elprup

Answer 1

现在最快的做法如下所示：现在

CREATE TABLE my_new_schema.my_table (LIKE my_old_schema.my_table);
ALTER TABLE my_new_schema.my_table APPEND FROM my_old_schema.my_table;
DROP TABLE my_old_schema.my_table;

在这种情况下，my_old_schema.my_table的数据被简单地重新映射为属于my_new_schema.my_table。比进行INSERT INTO要快得多。

重要提示：“将数据成功追加到目标表后，源表将为空”（来自AWS对ALTER TABLE APPEND的文档），因此请小心仅运行一次ALTER语句！

请注意，您可能需要删除并重新创建依赖于my_old_schema.my_table的任何视图。更新：如果您经常这样做，应使用WITH NO SCHEMA BINDING创建视图，它们将继续指向正确的表而无需重新创建。

- Joe Harris

---

由于视图存在问题，我建议创建“WITH NO SCHEMA BINDING”视图。 - dsz

采用这种方法需要注意的一点是，虽然它非常快，但在操作结束时也会清空源表。我曾经吃过亏，希望在执行 ALTER TABLE ... APPEND 操作两次并最终导致源表和目标表都被清空之前认真阅读了这个操作的 RTFM 步骤。答案是正确的。 - CodingInCircles

作为一个没有遭受过此类损失的人，我要说亚马逊允许你从一个空表格进行附加操作是不可原谅的，没有任何警告、确认或其他措施。甚至在信息中也没有任何提示告诉你你刚刚丢失了数据。简直是毫无考虑。 - DHW

Answer 2

最佳方法是创建一个具有所需架构的新表，然后使用旧表中的数据进行INSERT .... SELECT。

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

- Iñaki Soria

---

注意，如果您使用 create newschema.table1 (like table1); 创建新表，则 sortkey、distkey 和约束将被复制，但主键和外键不会（它们只是信息而已）。您也可以使用 select * into newschema.table1 from table1; 将表复制到新模式中，而无需先创建新表，但这不会复制非空约束、主键和外键。 - Dmitrii I.

@DmitriiI。这个查询语句是否也会复制与旧表相关的授权？ - himanshu219

Answer 3

您可以使用以下命令创建一个新表：CREATE TABLE schema1.tableName( LIKE schema2.tableName INCLUDING DEFAULTS ) ; ，然后使用INSERT INTO语句将一个架构中的表的内容复制到另一个架构中，最后使用DROP TABLE删除表。

- Ganesh Todkar

Answer 4

这是我的做法。

-- 如果你已经有一个备份，请删除它

DROP TABLE IF EXISTS TABLE_NAME_BKP CASCADE;

-- 创建两个备份，一个用于工作并将在最后被删除，另一个是真正的备份

SELECT * INTO TABLE_NAME_BKP FROM TABLE_NAME;
SELECT * INTO TABLE_NAME_4_WORK FROM TABLE_NAME;

--我们也可以执行以下ALTER操作，但这将保留主键约束名称，因此您无法使用相同的约束名称创建新表

ALTER TABLE TABLE_NAME RENAME TO TABLE_NAME_4_WORK;

-- 确保你已经复制了

SELECT COUNT(*) FROM TABLE_NAME;
SELECT COUNT(*) FROM TABLE_NAME_4_WORK; 

-- 创建新的表模式

    DROP TABLE IF EXISTS TABLE_NAME CASCADE;

    CREATE TABLE TABLE_NAME     (
       ID           varchar(36)     NOT NULL,
       OLD_COLUMN   varchar(36),
       NEW COLUMN_1 varchar(36)
    )
    compound sortkey (ID, OLD_COLUMN, NEW COLUMN_1);

    ALTER TABLE TABLE_NAME
    ADD CONSTRAINT PK__TAB_NAME__ID
    PRIMARY KEY (id);

-- 将数据从旧的复制到新的

INSERT INTO TABLE_NAME (
     id,    
     OLD_COLUMN)
 (SELECT     
     id,        
     OLD_COLUMN FROM TABLE_NAME_4_WORK) 

-- 删除工作表TABLE_NAME_4_WORK

 DROP TABLE TABLE_NAME_4_WORK;

-- 比较备份和新表行，并保留备份表一段时间。

 SELECT COUNT(*) FROM TABLE_NAME_BKP;

 SELECT COUNT(*) FROM TABLE_NAME;

- Kanagavelu Sugumar