Insertint into child tables with increasing itemNumber, PostgreSQL

Language: Layout:

CREATE SCHEMA o ;
SET search_path = o ;

DROP TABLE IF EXISTS "OrderItem";
DROP TABLE IF EXISTS "Order";

CREATE TABLE "Order"
(
    "orderId" integer primary key,  -- this should probably be serial
    "order" text not null
) ;

CREATE TABLE "OrderItem"
(
    "orderId" integer not null REFERENCES "Order"("orderId") ON DELETE CASCADE ON UPDATE CASCADE,
    "itemNumber" integer not null,
    "orderItem" text,
    
    PRIMARY KEY ("orderId", "itemNumber")
) ;

INSERT INTO "Order" ("orderId", "order") VALUES(1, 'Order number 1') ;
INSERT INTO "Order" ("orderId", "order") VALUES(2, 'Order number 2') ;

WITH "orderItemsToAdd" ("orderId", "orderItem") AS
(
    VALUES 
    (1, 'first item, 1st order'), 
    (1, 'second item, 1st order'),
    (2, 'first item, 2nd order'),
    (2, 'second item, 2nd order'),
    (2, 'third item, 2nd order')
)
, "maxItemNumbers" AS
(
SELECT
        "orderId", coalesce(max("itemNumber"), 0) AS "baseItemNumber"
FROM
        "orderItemsToAdd"
        LEFT JOIN "OrderItem" USING("orderId")
GROUP BY
        "orderId"                    
)
    
INSERT INTO 
    "OrderItem" ("orderId", "itemNumber", "orderItem")
SELECT
    "orderId", "baseItemNumber" + (row_number() over (partition by "orderId")), "orderItem"
FROM
    "orderItemsToAdd" 
    JOIN "maxItemNumbers" USING("orderId")
RETURNING
    * ;
    
DROP SCHEMA o CASCADE ;


absolute service time: 0,37 sec