sql - 使用 SQL 完全复制 postgres 表

Question

免责声明:这个问题类似于堆栈溢出问题here ，但这些答案都不能解决我的问题，我稍后会解释。

我正在尝试在 postgres 中复制一个大表(约 4000 万行，100 多列)，其中很多列都已编入索引。目前我使用这段 SQL:

CREATE TABLE <tablename>_copy (LIKE <tablename> INCLUDING ALL);
INSERT INTO <tablename>_copy SELECT * FROM <tablename>;

这个方法有两个问题:

1. 它在数据摄取之前添加索引，因此这比创建没有索引的表然后在复制所有数据后建立索引花费的时间要长得多。
2. 这不会正确复制“SERIAL”样式的列。它不是在新表上设置新的“计数器”，而是将新表中列的默认值设置为过去表的计数器，这意味着它不会随着行的添加而递增。

表的大小使索引成为一个实时问题。它还使转储到文件然后重新摄取变得不可行。我也没有命令行的优势。我需要在 SQL 中执行此操作。

我想做的是直接用一些神奇的命令制作一个精确的副本，或者如果那不可能，复制带有所有约束但没有索引的表，并确保它们是精神上的约束'(又名 SERIAL 列的新计数器)。然后使用 SELECT * 复制所有数据，然后复制所有索引。

来源

1. Stack Overflow question about database copying : 由于三个原因，这不是我要的

   • 它使用命令行选项 pg_dump -t x2 | sed 's/x2/x3/g' | psql 并且在此设置中我无权访问命令行
   • 它在数据摄取前创建索引，速度很慢
   • 它没有正确更新序列列作为 default nextval('x1_id_seq'::regclass)
   的证据

2. Method to reset the sequence value for a postgres table :这很棒，但不幸的是它非常手动。

Answer 1

PostgreSQL 中的create table as 功能现在可能是 OP 正在寻找的答案。

https://www.postgresql.org/docs/9.5/static/sql-createtableas.html

create table my_table_copy as
  select * from my_table

这将创建一个包含数据的相同表格。

添加没有数据将复制没有数据的模式。

create table my_table_copy as
  select * from my_table
with no data

这将创建包含所有数据的表，但没有索引和触发器等。

---

创建表 my_table_copy(如 my_table 包括所有)

create table like 语法将包括所有触发器、索引、约束等，但不包括数据。