postgreSQL：如何复制一行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15429756/
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
postgreSQL: how to duplicate a row
提问by clime
This is the scheme of my table web_book:
这是我的表 web_book 的方案：
Column | Type | Modifiers
----------------+------------------------+-------------------------------------------------------
id | integer | not null default nextval('web_book_id_seq'::regclass)
page_count | integer | not null
year_published | integer | not null
file | character varying(100) | not null
image | character varying(100) | not null
display_on_hp | boolean | not null
name | character varying(128) | not null
description | text | not null
name_cs | character varying(128) |
name_en | character varying(128) |
description_cs | text |
description_en | text |
The table contains one row with id=3. I want to duplicate the row but If I try this:
该表包含一行id=3。我想复制该行，但如果我尝试这样做：
INSERT INTO web_book SELECT * FROM web_book WHERE id=3;
I get this:
我明白了：
ERROR: duplicate key value violates unique constraint "web_book_pkey"
DETAIL: Key (id)=(3) already exists
回答by a_horse_with_no_name
You need to create a new ID for the newly inserted row:
您需要为新插入的行创建一个新 ID：
INSERT INTO web_book(
id, page_count, year_published, file, image,
display_on_hp, name, description, name_cs,
name_en, description_cs, description_en
)
SELECT nextval('web_book_id_seq'),
page_count,
year_published,
file,
image,
display_on_hp,
name,
description,
name_cs,
name_en,
description_cs,
description_en
FROM web_book WHERE id=3;
As mentioned by ClodoaldoNeto you can make things a bit easier by simply leaving out the ID column and let the default definition do it's job:
正如 ClodoaldoNeto 所提到的，您可以通过简单地省略 ID 列并让默认定义完成它的工作来使事情变得更容易：
INSERT INTO web_book(
page_count, year_published, file, image,
display_on_hp, name, description, name_cs,
name_en, description_cs, description_en
)
SELECT page_count,
year_published,
file,
image,
display_on_hp,
name,
description,
name_cs,
name_en,
description_cs,
description_en
FROM web_book WHERE id=3;
In this case you don't need to know the sequence name (but it is a bit less obvious what's going on).
在这种情况下，您不需要知道序列名称（但不太清楚发生了什么）。
回答by user2148736
Specify idcolumn only if you specify its value (and it's not your case). You want to use next sequence web_book_id_seqvalue, so do not specify it in your INSERT query.
id仅当您指定列的值时才指定列（这不是您的情况）。您想使用下一个序列web_book_id_seq值，因此不要在 INSERT 查询中指定它。
Your INSERT should looks like this:
您的 INSERT 应如下所示：
INSERT INTO web_book (page_count, year_published, file, image, display_on_hp, name, description, name_cs, name_en, description_cs, description_en)
SELECT page_count, year_published, file, image, display_on_hp, name, description, name_cs, name_en, description_cs, description_en
FROM web_book
WHERE id = 3;
