Copy data on parent and child table

I have two database tables. What I need to do is to copy specific data from one storage to another, but also keep the mapping to the photos. First part I can do easily writing

INSERT INTO item (storage_id, price, quantity, description, document_id)
SELECT 10, price, quantity, description, document_id
FROM item
WHERE quantity >= 10 AND price <= 100

but after that newly inserted items does not have photos. Note, that document_id field is unique for not copied items.

Answer

Assuming id columns are auto-generated surrogate primary keys, like a serial or IDENTITY column.

Use a data-modifying CTE with RETURNING to make do with a single scan on each source table:

WITH sel AS (
   SELECT id, price, quantity, description, document_id
   FROM   item
   WHERE  quantity >= 10
   AND    price <= 100
   )
, ins_item AS (
   INSERT INTO item
         (storage_id, price, quantity, description, document_id)
   SELECT 10        , price, quantity, description, document_id
   FROM   sel
   RETURNING id, document_id  -- document_id is UNIQUE in this set!
   )
INSERT INTO item_photo
      (item_id,    date,    size)
SELECT ii.id  , ip.date, ip.size
FROM  ins_item   ii
JOIN  sel        s  USING (document_id)   -- link back to org item.id
JOIN  item_photo ip ON ip.item_id = s.id; -- join to org item.id

CTE sel reads all we need from table items.

CTE ins_item inserts into table item. The RETURNING clause returns newly generated id values, together with the (unique!) document_id, so we can link back.

Finally, the outer INSERT inserts into item_photo. We can select matching rows after linking back to the original item.id.

Related:

• Insert New Foreign Key Row for new Strings

But:

document_id field is unique for not copied items.

Does that guarantee we are dealing with unique document_id values?