sql - 在 postgres 中复制一个表(包括索引)

Question

我有一个 postgres 表。我需要从中删除一些数据。我打算创建一个临时表，复制数据，重新创建索引并删除我需要的行。我不能删除原表的数据，因为这张原表是数据的来源。在一种情况下，我需要获得一些依赖于删除 X 的结果，在另一种情况下，我需要删除 Y。因此我需要所有原始数据始终可用。

然而，重新创建表并再次复制它并重新创建索引似乎有点愚蠢。 postgres 中有没有告诉它“我想要这个表的一个完整的单独副本，包括结构、数据和索引”？

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

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)

如果您使用的是 8.3 之前的 PostgreSQL，您可以简单地使用带选项“-t”的 pg_dump 来指定 1 个表，在转储中更改表名，然后再次加载它:

=> 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)