postgreSQL：如何复制一行 [英] postgreSQL: how to duplicate a row

问题描述

这是我的表web_book的方案：

This is the scheme of my table 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                   |

该表包含一行，其中 id = 3 。我想复制该行，但是如果我尝试这样做：

The table contains one row with id=3. I want to duplicate the row but If I try this:

INSERT INTO web_book SELECT * FROM web_book WHERE id=3;

我明白了：

ERROR:  duplicate key value violates unique constraint "web_book_pkey"
DETAIL:  Key (id)=(3) already exists

推荐答案

您需要为新插入的行创建一个新ID：

You need to create a new ID for the newly inserted row:

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;

如ClodoaldoNeto所述，您可以通过简单地省略ID列并让默认定义是否起作用：

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:

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

这篇关于postgreSQL：如何复制一行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！