SQL 在 postgres 中复制一个表（包括索引）
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/198141/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Copy a table (including indexes) in postgres
提问by Rory
I have a postgres table. I need to delete some data from it. I was going to create a temporary table, copy the data in, recreate the indexes and the delete the rows I need. I can't delete data from the original table, because this original table is the source of data. In one case I need to get some results that depends on deleting X, in another case, I'll need to delete Y. So I need all the original data to always be around and available.
我有一个 postgres 表。我需要从中删除一些数据。我打算创建一个临时表，复制数据，重新创建索引并删除我需要的行。我不能从原始表中删除数据，因为这个原始表是数据源。在一种情况下，我需要得到一些取决于删除 X 的结果，在另一种情况下，我需要删除 Y。所以我需要所有原始数据始终存在且可用。
However it seems a bit silly to recreate the table and copy it again and recreate the indexes. Is there anyway in postgres to tell it "I want a complete separate copy of this table, including structure, data and indexes"?
然而，重新创建表并再次复制它并重新创建索引似乎有点愚蠢。无论如何，postgres 是否会告诉它“我想要这个表的完整单独副本，包括结构、数据和索引”？
Unfortunately PostgreSQL does not have a "CREATE TABLE .. LIKE X INCLUDING INDEXES'
不幸的是，PostgreSQL 没有“CREATE TABLE .. LIKE X INCLUDING INDEXES”
回答by
New PostgreSQL ( since 8.3 according to docs ) can use "INCLUDING INDEXES":
新的 PostgreSQL（根据文档从 8.3 开始）可以使用“包含索引”：
# 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)
As you can see I'm testing on 8.3.
如您所见，我正在 8.3 上进行测试。
Now, let's create table:
现在，让我们创建表：
# 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
And see how it looks:
看看它的样子：
# \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)
Now we can copy the structure:
现在我们可以复制结构：
# 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
And check the structure:
并检查结构：
# \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)
If you are using PostgreSQL pre-8.3, you can simply use pg_dump with option "-t" to specify 1 table, change table name in dump, and load it again:
如果您使用的是 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
And now the table is:
现在表格是：
# \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)
回答by WolfmanDragon
[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]
Here is an example
这是一个例子
CREATE TABLE films_recent AS
SELECT * FROM films WHERE date_prod >= '2002-01-01';
The other way to create a new table from the first is to use
从第一个创建新表的另一种方法是使用
CREATE TABLE films_recent (LIKE films INCLUDING INDEXES);
INSERT INTO films_recent
SELECT *
FROM books
WHERE date_prod >= '2002-01-01';
Note that Postgresql has a patchout to fix tablespace issues if the second method is used
请注意，如果使用第二种方法，Postgresql 有一个补丁来修复表空间问题
回答by Alan
I have a postgres table. I need to delete some data from it.
我有一个 postgres 表。我需要从中删除一些数据。
I presume that ...
我认为...
delete from yourtable
where <condition(s)>
... won't work for some reason. (Care to share that reason?)
......由于某种原因不起作用。（愿意分享这个原因吗？）
I was going to create a temporary table, copy the data in, recreate the indexes and the delete the rows I need.
我打算创建一个临时表，复制数据，重新创建索引并删除我需要的行。
Look into pg_dump and pg_restore. Using pg_dump with some clever options and perhaps editing the output before pg_restoring might do the trick.
查看 pg_dump 和 pg_restore。使用带有一些聪明选项的 pg_dump 并可能在 pg_restoreing 之前编辑输出可能会起作用。
Since you are doing "what if"-type analysis on the data, I wonder if might you be better off using views.
由于您正在对数据进行“假设”类型的分析，我想知道您是否最好使用视图。
You could define a view for each scenario you want to test based on the negation of what you want to exclude. I.e., define a view based on what you want to INclude. E.g., if you want a "window" on the data where you "deleted" the rows where X=Y, then you would create a view as rows where (X != Y).
您可以根据要排除的内容的否定为要测试的每个场景定义一个视图。即，根据您要包含的内容定义视图。例如，如果您想要在“删除”X=Y 行的数据上有一个“窗口”，那么您将创建一个视图作为行（X ！= Y）。
Views are stored in the database (in the System Catalog) as their defining query. Every time you query the view the database server looks up the underlying query that defines it and executes that (ANDed with any other conditions you used). There are several benefits to this approach:
视图作为其定义查询存储在数据库中（在系统目录中）。每次查询视图时，数据库服务器都会查找定义它的基础查询并执行该查询（与您使用的任何其他条件进行 AND 运算）。这种方法有几个好处：
- You never duplicate any portion of your data.
- The indexes already in use for the base table (your original, "real" table) will be used (as seen fit by the query optimizer) when you query each view/scenario. There is no need to redefine or copy them.
- Since a view is a "window" (NOT a shapshot) on the "real" data in the base table, you can add/update/delete on your base table and simply re-query the view scenarios with no need to recreate anything as the data changes over time.
- 您永远不会复制数据的任何部分。
- 当您查询每个视图/场景时，将使用已用于基表（您的原始“真实”表）的索引（如查询优化器认为合适）。无需重新定义或复制它们。
- 由于视图是基表中“真实”数据的“窗口”（不是快照），因此您可以在基表上添加/更新/删除，只需重新查询视图场景，无需重新创建任何内容数据随时间变化。
There is a trade-off, of course. Since a view is a virtual table and not a "real" (base) table, you're actually executing a (perhaps complex) query every time you access it. This may slow things down a bit. But it may not. It depends on many issues (size and nature of the data, quality of the statistics in the System Catalog, speed of the hardware, usage load, and much more). You won't know until you try it. If (and only if) you actually find that the performance is unacceptably slow, then you might look at other options. (Materialized views, copies of tables, ... anything that trades space for time.)
当然，有一个权衡。由于视图是虚拟表而不是“真实”（基）表，因此每次访问它时实际上都在执行（可能是复杂的）查询。这可能会减慢速度。但它可能不是。这取决于许多问题（数据的大小和性质、系统目录中的统计质量、硬件速度、使用负载等等）。你不试试就知道了。如果（且仅当）您确实发现性能慢得令人无法接受，那么您可能会考虑其他选项。（物化视图，表格副本，......任何以空间换时间的东西。）
回答by Alan
Create a new table using a select to grab the data you want. Then swap the old table with the new one.
使用选择创建一个新表以获取您想要的数据。然后将旧表与新表交换。
create table mynewone as select * from myoldone where ...
mess (re-create) with indexes after the table swap.
回答by oshai
There are many answers on the web, one of them can be found here.
网上有很多答案，其中之一可以在这里找到。
I ended up doing something like this:
我最终做了这样的事情：
create table NEW ( like ORIGINAL including all);
insert into NEW select * from ORIGINAL
This will copy the schema and the data including indexes, but not including triggers and constraints. Note that indexes are shared with original table so when adding new row to either table the counter will increment.
这将复制架构和数据，包括索引，但不包括触发器和约束。请注意，索引与原始表共享，因此当向任一表添加新行时，计数器将增加。
回答by oshai
There are many answers on the web, one of them can be found here.
网上有很多答案，其中之一可以在这里找到。
I ended up doing something like this:
我最终做了这样的事情：
create table NEW ( like ORIGINAL including all);
insert into NEW select * from ORIGINAL
This will copy the schema and the data including indexes, but not including triggers and constraints. Note that indexes are shared with original table so when adding new row to either table the counter will increment.
这将复制架构和数据，包括索引，但不包括触发器和约束。请注意，索引与原始表共享，因此当向任一表添加新行时，计数器将增加。
See also the answer here.
另请参阅此处的答案。
回答by Ringtail
A simple way is include all:
一个简单的方法是包括所有：
CREATE TABLE new_table (LIKE original_table INCLUDING ALL);
