在Postgres中复制一个表（包括索引）

Question

我有一个postgres表，需要从中删除一些数据。我原本想创建一个临时表，将数据复制到其中，在重新创建索引后删除所需的行。我无法从原始表中删除数据，因为这个原始表是数据来源。在一个情况下，我需要得到一些取决于删除X的结果，在另一个情况下，我将需要删除Y。因此，我需要始终保留和可用所有原始数据。

然而，重新创建表并再次复制表格并重新创建索引似乎有点儿傻。在Postgres中是否有任何方法可以告诉它，“我想要完全独立的此表格副本，包括结构、数据和索引”？

不幸的是，PostgreSQL没有“CREATE TABLE.. LIKE X INCLUDING INDEXES”的功能。

- Rory

Answer 1

新的PostgreSQL（根据文档从8.3版本开始）可以使用“INCLUDING INDEXES”：

# select version();
                                             version
-------------------------------------------------------------------------------------------------
 PostgreSQL 8.3.7 on x86_64-pc-linux-gnu, compiled by GCC cc (GCC) 4.2.4 (Ubuntu 4.2.4-1ubuntu3)
(1 row)

您可以看到我正在测试8.3版本。

现在，让我们创建表格：

# create table x1 (id serial primary key, x text unique);
NOTICE:  CREATE TABLE will create implicit sequence "x1_id_seq" for serial column "x1.id"
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "x1_pkey" for table "x1"
NOTICE:  CREATE TABLE / UNIQUE will create implicit index "x1_x_key" for table "x1"
CREATE TABLE

看看它的样子：

# \d x1
                         Table "public.x1"
 Column |  Type   |                    Modifiers
--------+---------+-------------------------------------------------
 id     | integer | not null default nextval('x1_id_seq'::regclass)
 x      | text    |
Indexes:
    "x1_pkey" PRIMARY KEY, btree (id)
    "x1_x_key" UNIQUE, btree (x)

现在我们可以复制这个结构：

# create table x2 ( like x1 INCLUDING DEFAULTS INCLUDING CONSTRAINTS INCLUDING INDEXES );
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "x2_pkey" for table "x2"
NOTICE:  CREATE TABLE / UNIQUE will create implicit index "x2_x_key" for table "x2"
CREATE TABLE

请检查结构：

# \d x2
                         Table "public.x2"
 Column |  Type   |                    Modifiers
--------+---------+-------------------------------------------------
 id     | integer | not null default nextval('x1_id_seq'::regclass)
 x      | text    |
Indexes:
    "x2_pkey" PRIMARY KEY, btree (id)
    "x2_x_key" UNIQUE, btree (x)

如果您使用的是 PostgreSQL 8.3 之前的版本，您可以使用 pg_dump 命令并加上 "-t" 选项指定一个表，在导出时修改表名，并再次加载：

=> pg_dump -t x2 | sed 's/x2/x3/g' | psql
SET
SET
SET
SET
SET
SET
SET
SET
CREATE TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE

现在表格如下:

# \d x3
                         Table "public.x3"
 Column |  Type   |                    Modifiers
--------+---------+-------------------------------------------------
 id     | integer | not null default nextval('x1_id_seq'::regclass)
 x      | text    |
Indexes:
    "x3_pkey" PRIMARY KEY, btree (id)
    "x3_x_key" UNIQUE, btree (x)

- user80168

---

这样主键序列（x1_id_seq）将在两个表之间共享！ - Jauzsika

使用pg9.X时，如果使用"INCLUDING CONSTRAINTS"（而非"INCLUDING INDEXES"），主键序列将会被共享。 - Peter Krauss

Answer 2

[CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } ] TABLE table_name
    [ (column_name [, ...] ) ]
    [ WITH ( storage_parameter [= value] [, ... ] ) | WITH OIDS | WITHOUT OIDS ]
    [ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]
    [ TABLESPACE tablespace ]
    AS query][1]  

这里有一个示例

CREATE TABLE films_recent AS
  SELECT * FROM films WHERE date_prod >= '2002-01-01';

从第一个表创建新表的另一种方法是使用

    CREATE TABLE films_recent (LIKE films INCLUDING INDEXES);  

    INSERT INTO films_recent
         SELECT *
           FROM books
          WHERE date_prod >= '2002-01-01';  

请注意，如果使用第二种方法，则Postgresql有一个补丁可解决表空间问题。

- WolfmanDragon

---

Postgres 中没有 "INCLUDING INDEXES"。 - Amandasaurus

你使用的是哪个版本？阅读最新文档，它就在那里。 - WolfmanDragon

使用pg9.X时，如果使用"INCLUDING CONSTRAINTS"（而不是"INCLUING INDEXES"），则两个表之间的主键序列将被共享。 - Peter Krauss

看起来可能需要使用CREATE TABLE my_table (LIKE...)而不是CREATE TABLE my_table LIKE...才能使其正常工作。已编辑答案。 - Jason Swett

@PeterKrauss，你解决了共享主键序列的问题吗？我正在尝试将一堆数据复制到新表中。我无法删除旧表并重命名新表，因为新表的主键指向旧表。 - yellottyellott

第一种方法在我的端上没有复制索引（PostgreSQL 11）。 - Rafs

Answer 3

互联网上有很多答案，其中一个可以在这里找到。

我最终做了类似于以下操作：

create table NEW ( like ORIGINAL including all);
insert into NEW select * from ORIGINAL

这将复制模式和数据，包括索引，但不包括触发器和约束条件。 请注意，索引与原始表共享，因此向任一表中添加新行时，计数器将增加。

- oshai

---

根据 https://www.postgresql.org/docs/current/sql-createtable.html 的说明，“请注意，索引与原始表共享，因此向任一表添加新行时，计数器将增加。”这句话是错误的吗？ - LingYan Meng

Answer 4

一种简单的方法是将所有内容都包含在内：

CREATE TABLE new_table (LIKE original_table INCLUDING ALL);

- Ringtail

---

版本9.1+支持此语法。 - vinh

请注意，这将创建一个空表。 - dcc310

Answer 5

我有一个postgres表，需要从中删除一些数据。

我认为...

delete from yourtable
where <condition(s)>

由于某些原因，“...”无法正常工作。（能否分享一下这个原因？）

我打算创建一个临时表，将数据复制进去，重新创建索引，然后删除我需要的行。

研究一下pg_dump和pg_restore。使用一些巧妙的选项，或许在pg_restoring之前编辑输出可能会有帮助。

既然你正在对数据进行“假设”分析，我想你是否最好使用视图。

你可以为每个要测试的场景定义一个视图，该视图基于你想要排除的内容的否定。也就是说，基于你想要包含的东西定义一个视图。例如，如果您想在数据上获得一个窗口，在此窗口中，您已经“删除”了X = Y的行，则应基于（X！=Y）的行创建一个视图。

视图作为其定义查询存储在数据库（系统目录）中。每次查询视图时，数据库服务器都会查找定义它的底层查询并执行它（与您使用的任何其他条件AND）。这种方法有几个好处：

1. 您永远不会复制任何部分的数据。

2. 在查询每个视图/方案时，已经在使用的基本表（即您的原始“真实”表）中使用的索引将被使用（由查询优化器确定）。无需重新定义或复制它们。

3. 由于视图是基本表（而不是快照）中“真实”数据的“窗口”，因此您可以在基本表上添加/更新/删除，并仅仅重新查询视图方案，而无需在数据随时间变化时重新创建任何内容。

当然，存在一个权衡。由于视图是虚拟表而不是真正的（基本）表，因此每次访问它时实际上都要执行一个（可能是复杂的）查询。这可能会稍微减慢一些速度。但是可能也不是。这取决于许多问题（数据的大小和性质，系统目录中统计信息的质量，硬件的速度，使用负载等等）。在尝试之前，您将不会知道。如果（并且仅当）您实际发现性能过慢，则可能查看其他选项。（材料化视图，表的副本，...任何以空间换时间的东西。）

- Alan

---

我已更新问题，说明为什么我不能只从原始表中删除。 - Amandasaurus

Answer 6

创建一个新的表格，使用 select 命令来选择想要的数据。然后用新表格替换旧的表格。

create table mynewone as select * from myoldone where ...
mess (re-create) with indexes after the table swap.

- jim