postgreSQL: how to duplicate a row

postgresql

Solution

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;

As mentioned by ClodoaldoNeto you can make things a bit easier by simply leaving out the ID column and let the default definition do its 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).

Problem

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 | ``` 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; ``` I get this: ``` ERROR: duplicate key value violates unique constraint "web_book_pkey" DETAIL: Key (id)=(3) already exists ```

Original source