完全复制一个PostgreSQL表的SQL

Question

免责声明：这个问题与Stack Overflow上的问题类似，但是那里的答案都不适用于我的问题，我稍后会解释。

我正在尝试在Postgres中复制一个大表格（约40M行，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问题：这不是我要求的三个原因
   • 它使用命令行选项 pg_dump -t x2 | sed 's/x2/x3/g' | psql ，在这种情况下我无法访问命令行
   • 它在数据导入之前创建索引，这很慢
   • 正如 default nextval('x1_id_seq'::regclass) 所证明的那样，它没有正确更新序列列
2. 重置Postgres表序列值的方法：这很好，但不幸的是它非常手动。

- Erik

---

你的问题很可能是 https://dev59.com/h3VC5IYBdhLWcg3wvT1a 的重复。 - goodside

我看到了那个问题，没有令人满意的答案可以真正做到我所请求的，但这促使我对我的帖子进行另一个编辑。 - Erik

那个页面上得票最高的解决方案存在三个主要问题。第一，他们在 pg_dump -t x2 | sed 's/x2/x3/g' | psql 中使用命令行函数，而我无法访问。第二，在添加数据之前创建索引会非常慢！第三，SERIAL 的默认参数仍然引用第一个表 default nextval('x1_id_seq'::regclass)。这些都是我在问题中已经指出的三个缺陷。你告诉我没有任何解决方案吗？@peter - Erik

这只是一件小小的编程事情。 - Peter Eisentraut

Answer 1

在PostgreSQL中，create table as功能现在可能是问题的答案。

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

create table my_table_copy as
  select * from my_table

这将创建一个具有相同数据的表。

添加with no data将复制架构而不包括数据。

create table my_table_copy as
  select * from my_table
with no data

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

---

create table my_table_copy (like my_table including all)

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

- Phill

---

我之前发布了这个问题，但是已经很久以前了，我无法轻易地进行验证。不过，“create table as”语句似乎并不会复制与该表相关的其他对象，例如索引和序列。 - Erik

啊，你是对的@Erik。真遗憾。我会留下我的答案，以防其他人发现它有用。但是我已经添加了一条注释说它不会复制其他信息。谢谢。 - Phill

的确，这非常有帮助，可以指出限制。现在我们知道在使用这种方法时要注意什么。谢谢！ - frostymarvelous

“(like my_table including all)” 可能不能满足那位不想要索引的 OP，但对于我想要获取所有约束条件的表来说，它是完美的。 - Noumenon

Answer 2

不幸的是，你需要手动完成其中的一些操作。但所有操作都可以在类似 psql 的工具中执行。第一个命令非常简单：

select * into newtable from oldtable

这将创建一个包含与旧表相同数据但不包含索引的新表。您需要自己创建索引和序列等。您可以使用以下命令获取表中所有索引的列表:

select indexdef from pg_indexes where tablename='oldtable';

然后运行 psql -E 来访问你的数据库并使用 \d 查看旧表。你可以将这两个查询混合在一起，以获取有关序列的信息：

SELECT c.oid,
  n.nspname,
  c.relname
FROM pg_catalog.pg_class c
     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relname ~ '^(oldtable)$'
  AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 2, 3;

SELECT a.attname,
  pg_catalog.format_type(a.atttypid, a.atttypmod),
  (SELECT substring(pg_catalog.pg_get_expr(d.adbin, d.adrelid) for 128)
   FROM pg_catalog.pg_attrdef d
   WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum AND a.atthasdef),
  a.attnotnull, a.attnum
FROM pg_catalog.pg_attribute a
WHERE a.attrelid = '74359' AND a.attnum > 0 AND NOT a.attisdropped
ORDER BY a.attnum;

将上面的74359替换为您从前一个查询中获得的oid。

- Scott Marlowe

---

请注意，如果您希望这些序列依赖于新的父表，则必须使用“alter sequence seqname owned by newtable.column;”。 - Scott Marlowe

Answer 3

最接近的“奇迹命令”类似于：

pg_dump -t tablename | sed -r 's/\btablename\b/tablename_copy/' | psql -f -

特别是，在加载表数据后，它会负责创建索引。

但这不会重置序列号；您需要自己编写脚本。

- Peter Eisentraut

---

请注意下面的重要警告（由Tomek提供）：如果您的数据恰好包含表名，这个命令将破坏您的数据！为了安全起见，我使用emacs手动搜索和替换，而不是sed。 - CPBL

Answer 4

要完全复制一个表，包括表结构和数据，您可以使用以下语句：

CREATE TABLE new_table AS 
TABLE existing_table;

要复制一个表的结构但不包括数据，你需要在CREATE TABLE语句中添加WITH NO DATA子句，具体如下：

CREATE TABLE new_table AS 
TABLE existing_table 
WITH NO DATA;

要从现有表中复制部分数据的表格，您可以使用以下语句：

CREATE TABLE new_table AS 
SELECT
*
FROM
    existing_table
WHERE
    condition;

- K M Rakibul Islam

---

你能提供一个参考链接吗？我在postgresql.org上没有找到任何关于这个语法的信息。一些值得注意的问题是：a）索引是否保留？b）这在哪些版本的Postgres中有效？ - Erik

创建新表 new_table，作为现有表 existing_table 的一个副本，但不包含数据； 这个操作不会复制表的完整结构（如索引、触发器、约束等）。 - Raju Ahmed

sql中的table命令在https://www.postgresql.org/docs/13/sql-select.html的**select命令**页面末尾有文档记录。`table x是select * from x`的简写。create table和create table as是具有不同语法的不同文档页面。请参见https://www.postgresql.org/docs/13/sql-createtable.html和https://www.postgresql.org/docs/13/sql-createtableas.html。上述查询仅获取列类型及其数据，但仍然非常有用。 - chipfall

Answer 5

警告：

所有使用pg_dump和任何正则表达式来替换源表名的答案都非常危险。如果您的数据包含您尝试替换的子字符串，那么您最终会改变您的数据！

我提出了一个两步解决方案：

1. 使用一些特定于数据的正则表达式从转储中消除数据行。
2. 对剩余的行执行搜索和替换。

以下是Ruby示例：

ruby -pe 'gsub(/(members?)/, "\\1_copy_20130320") unless $_ =~ /^\d+\t.*(?:t|f)$/' < members-production-20130320.sql > copy_members_table-20130320.sql

我想将“members”表复制到“members_copy_20130320”中。我的数据特定正则表达式为 /^\d+\t.*(?:t|f)$/

上述类型的解决方案对我有效。购买时请注意...

编辑：

好的，这里是另一种伪shell语法的方法，适用于不喜欢正则表达式的人：

1. pg_dump -s -t mytable mydb > mytable_schema.sql
2. 在mytable_schema.sql中搜索和替换表名 > mytable_copy_schema.sql

3. psql -f mytable_copy_schema.sql mydb

4. pg_dump -a -t mytable mydb > mytable_data.sql

5. 在数据部分前几个SQL语句中替换"MyTable"
6. psql -f mytable_data.sql mydb

- Tomek

Answer 6

create table newTableName (like   oldTableName including indexes); 
insert into newTableName  select * from oldTableName

这对我有效 9.3

- user2940756

---

此方法不包括外键。 - Frank N Stein

Answer 7

显然，您想要“重建”一张表。如果您只想重建一张表，而不是复制它，那么您应该使用CLUSTER。

SELECT count(*) FROM table; -- make a seq scan to make sure the table is at least
                            -- decently cached
CLUSTER someindex ON table;

您可以选择索引，尽量选择适合您查询的索引。如果没有其他合适的索引，您始终可以使用主键。

如果您的表太大无法缓存，则 CLUSTER 可能会变慢。

- bobflux

---

我确实想要复制，我删除了与问题无关的额外代码。据我所知，CLUSTER只是根据索引重新排序行，这并不是我要找的。对于错误信息，我感到抱歉。 - Erik