rollback;

begin;

create table tree(
    id uuid not null,
    parent uuid,
    "order" integer not null,
    primary key(id),
    foreign key(parent) references tree,
    exclude ((coalesce(parent, uuid_nil())) with =, "order" with =) deferrable
);

with parent as(
    select uuid_generate_v4() as id
    , row_number() over() as rn
    from generate_series(1, 5)
), child as (
    select uuid_generate_v4() as id
    , parent.id as parent_id
    , row_number() over(partition by parent.id) as rn
    from parent, generate_series(1, 5)
) insert into tree(id, parent, "order") 
    select id, null, rn from parent
    union all
    select id, parent_id, rn from child;

update tree set "order" = 3 - "order" where "order" in(1, 2);

-- commit;