Copy rows and foreign key rows, keep relation/order

Question

I have two tables with a 1:1 relation (PostgreSQL 12):

CREATE TABLE public.objects (
    id uuid DEFAULT public.gen_random_uuid () NOT NULL PRIMARY KEY UNIQUE,
    name text NOT NULL,
    object_type_a_id uuid NOT NULL
);

CREATE TABLE public.object_type_a (
    id uuid DEFAULT public.gen_random_uuid () NOT NULL PRIMARY KEY UNIQUE,
    description text NOT NULL
);

ALTER TABLE ONLY public.objects
    ADD CONSTRAINT objects_object_type_a_id_fkey FOREIGN KEY (object_type_a_id) REFERENCES public.object_type_a (id) ON UPDATE RESTRICT ON DELETE RESTRICT;

INSERT INTO public.object_type_a (description) VALUES ('desc_1'), ('desc_2'), ('desc_3');
INSERT INTO objects (name, object_type_a_id) SELECT 'name_1', id FROM object_type_a WHERE description = 'desc_1';
INSERT INTO objects (name, object_type_a_id) SELECT 'name_2', id FROM object_type_a WHERE description = 'desc_2';
INSERT INTO objects (name, object_type_a_id) SELECT 'name_3', id FROM object_type_a WHERE description = 'desc_3';

I now have to duplicate all rows in objects and object_type_a, keeping the relation as well as all values in objects.name and object_type_a.description so it would look like this:

objects
---------------------------------
id    | name   | object_type_a_id
---------------------------------
847c..| name_1 | 5b7d..
ae3e..| name_2 | 4ce4..
41fd..| name_3 | bffa..
d570..| name_1 | eeec..
4cfd..| name_2 | 4bb0..
892f..| name_3 | aeff..

object_type_a
--------------------
id    | description 
--------------------
5b7d..| desc_1
4ce4..| desc_2
bffa..| desc_3
eeec..| desc_1
4bb0..| desc_2
aeff..| desc_3

I tried using CTEs/subqueries, selecting all rows from objects and object_type_a, then inserting into / updating objects, but this is obviously going to mix up the relations and doing this one row at a time seems to be rather inefficient.

Answer 1

Assuming that description is unique in the "a" table, then you can take the following approach:

• Insert the new "a" objects into the "a" table.
• Look up the existing "a" object and then match to the newly inserted on based on the description.
• Use this match for the inserting rows.

Postgres allows CTEs with DML statements, returning the values of the changed rows, so this can all be done in one statement:

with oa as (
      insert into objects_type_a (description)
          select description
          from object_type_a oa
          where exists (select 1 from objects o where oa.id = o.object_type_a_id)
          returning *
     )
insert into objects (name, object_type_a_id)
    select o.name, oa.id
    from objects o join
         object_type_a oa_orig
         on o.object_type_a_id = oa_orig.id join
         oa
         on oa_orig.description = oa.description;

Here is a db<>fiddle.