SQLize | PHPize | SQLtest

create table project ( id bigint primary key generated by default as identity, name text ); create table project_line ( id bigint primary key generated by default as identity, project bigint not null references project(id), name text ); create table project_subline ( id bigint primary key generated by default as identity, line bigint references project_line(id) on delete cascade ); insert into project(name) values('proyecto'); insert into project_line(project,name) values(1,'patata'),(1,'pototo'); insert into project_subline(line) values(1); -- insert into line(id, name) select * from project_line; -- alter table project_line drop column name; -- alter table project_line alter column id drop identity; -- alter table project_line add constraint fk_line_id foreign key (id) references line(id); -- alter table line alter column id restart with 3; -- insert into line(name) values ('mandingo'),('mondongo'); -- alter table project_subline drop constraint project_subline_line_fkey; -- alter table project_subline add constraint fk_line foreign key(line) references line(id); -- alter table project_subline rename to subline; alter table project_line rename to line; create table project_line ( id bigint primary key references line(id) on delete cascade, project bigint not null references project(id) on delete cascade ); insert into project_line select id, project from line; alter table line drop column project; alter table project_subline rename to subline; select L.*, PL.*, array_agg(SL) from line L join project_line PL on L.id = PL.id full join subline SL on L.id = SL.line group by L.id, PL.id;