如何在Postgres中将某些表从一个模式复制到另一个模式而保留原始模式？

Question

我想将同一 Postgres 数据库中的 schema1 中的 4 张表复制到 schema2，同时也想保留这些数据在 schema1 的表。请问如何在 pgadmin 和 postgres 控制台中实现呢？

- MANU

---

“移动”一张表格意味着 不 保留原始表格，至少在我对“移动”的理解中是这样的。 - user330315

可能是在Postgres中复制表（包括索引）的重复问题。 - Kevin Brown-Silva

Answer 1

你可以使用 create table ... like 命令

create table schema2.the_table (like schema1.the_table including all);

然后将数据从源插入到目标位置：

insert into schema2.the_table
select * 
from schema1.the_table;

- user330315

---

只有包含引号时，它才能正常工作： create table new_schema."new_one" (like old_schema."old_table" including all) - Hrvoje

如果您需要使用引号，则"old_table"与old_table完全相同。这意味着您创建的表具有混合大小写和双引号，这是强烈不建议的做法（请参阅https://wiki.postgresql.org/wiki/Don't_Do_This#Don.27t_use_upper_case_table_or_column_names）。 - user330315

@a_horse_with_no_name 这个查询不会复制外键。但是它确实会复制唯一约束。 - Mazimia

Answer 2

您可以使用CREATE TABLE AS SELECT。这样您就不需要插入数据了，表将会带有数据被创建。

CREATE TABLE schema2.the_table
AS 
SELECT * FROM schema1.the_table;

- Alec

---

这个只需要一个查询就可以完成所有操作。 - MANU

@MANU: 但它不会复制约束和索引。 - user330315

@a_horse_with_no_name 是的，很好的观点。没错，那就是问题所在了。我的错误。 - MANU

如果您这样做，schema1.the_table 中的更改是否会自动更新到 schema2.the_table 中的数据？ - Pedro Cavalcante

不，Pedro，这份副本将是静态的。 - Benji York

Answer 3

从v12版本开始，可以使用简单的语法：

CREATE TABLE newSchema.newTable
AS TABLE oldSchema.oldTable;

- Kyouma

---

这个也不会复制约束和索引。 - dirkt

Answer 4

PG dump和PG restore通常是最有效的工具。

从命令行：

pg_dump --dbname=mydb --schema=my_schema --file=/Users/my/file.dump --format=c --username=user --host=myhost --port=5432

pg_restore --dbname=mydb --schema=my_schema --format=c --username=user --host=myhost --port=5432 /Users/my/file.dump --no-owner

- Ricardo Mayerhofer

Answer 5

这将循环遍历旧模式中的所有表，并在新模式中重新创建它们（没有约束、索引等）。

-- Set the search path to the target schema
SET search_path = newSchema;

-- Loop over the table names and recreate the tables
DO $$
DECLARE
  table_name text;
BEGIN
  FOR table_name IN
    SELECT t.table_name
    FROM information_schema.tables t
    WHERE t.table_schema = 'public'
      AND t.table_type = 'BASE TABLE'
  LOOP
    EXECUTE 'CREATE TABLE ' || quote_ident(table_name) || ' AS TABLE oldSchema.' || quote_ident(table_name);
  END LOOP;
END $$;

当您不需要附加到表上的所有附加功能，只想要完整数据的干净副本时，这特别适用于合并多个数据仓库模式。

- Daniel Nalbach