postgreSQL: how to duplicate a row

Question

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 

Answer 1

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

Answer 2

Specify id column only if you specify its value (and it's not your case). You want to use next sequence web_book_id_seq value, so do not specify it in your INSERT query.

Your INSERT should looks like this:

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;