Copy rows and foreign key rows, keep relation/order

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.

Solution

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.