如何替换postgres中的表？ [英] How do I replace a table in postgres?

问题描述

基本上我想这样做：

begin;
lock table a;
alter table a rename to b;
alter table a1 rename to a;
drop table b;
commit;

即获得控制权并替换我的旧表，而没有人可以访问它。

i.e. gain control and replace my old table while no one has access to it.

推荐答案

简单：

BEGIN;
DROP TABLE a;
ALTER TABLE a1 RENAME TO a;
COMMIT;

DROP TABLE 获得 ACCESS EXCLUSIVE 始终锁定在桌子上。显式的 LOCK 命令并不好。重命名一个死人只是浪费时间。

DROP TABLE acquires an ACCESS EXCLUSIVE lock on the table anyway. An explicit LOCK command is no better. And renaming a dead guy is just a waste of time.

尝试访问表的并发事务会发生什么？并不是那么简单，请阅读以下内容：

What happens to concurrent transactions trying to access the table? It's not that simple, read this:

• 在大型表格中填充新列的最佳方法？

• Best way to populate a new column in a large table?

解释为什么您可能会看到错误消息，例如

Explains why you may have seen error messages like this:

ERROR:  could not open relation with OID 123456

这篇关于如何替换postgres中的表？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！