Le portail francophone de la géomatique

drop table if exists parent cascade ;
create table parent (
    id int primary key ,
    nom text
);

insert into parent
values (1, 'toto'),
       (2, 'tata'),
       (3, 'popo');

drop table if exists enfant cascade ;
create table enfant(
    id int primary key ,
    id_p int references parent(id),
    dte date
);

insert into enfant values
(1, 2, '01/02/2020'),
(2, 1, '03/02/2020'),
(3, 3, '04/02/2020');

begin;
alter table enfant drop constraint enfant_id_p_fkey;
alter table parent alter column nom type text[] using array[nom];

-- insert into parent et enfant dans la meme requete pour garder le lien avec l'ancienne pk
with tmp as (
    insert into parent (id, nom)
           select (select max(id) + 1 from parent ) as id, array_agg(nom[1]) as nom
           from parent
           where id in (1, 2)
           -- ou where nom <@ '{toto, tata}'::text[]
       returning id, nom
), tmp1 as (
    update enfant e set id_p = tmp.id
    from tmp, parent p
    where p.nom <@ tmp.nom and e.id_p = p.id

)
delete from parent where id in (1, 2);

alter table enfant add constraint enfant_id_p_fk foreign key (id_p) references parent(id);

commit ;

table parent;
-- +--+-----------+
-- |id|nom        |
-- +--+-----------+
-- |3 |{popo}     |
-- |4 |{toto,tata}|
-- +--+-----------+

table enfant;
-- +--+----+----------+
-- |id|id_p|dte       |
-- +--+----+----------+
-- |1 |4   |2020-01-02|
-- |2 |4   |2020-03-02|
-- |3 |3   |2020-04-02|
-- +--+----+----------+