SQL commands to reorder data based on a 'position' column

reordering_linked-list.sql

drop table if exists stuff;

create table if not exists stuff (
    id bigserial primary key not null,
    name character varying (10) not null,
    position smallint not null default 0
);

create unique index if not exists name_idx on stuff (name asc);
create index if not exists position_idx on stuff (position asc);

insert into stuff (name, position) VALUES ('one', 0), ('two', 1), ('three', 2), ('four', 3), ('five', 4);
insert into stuff (name, position) VALUES ('six', coalesce((select max(position) + 1 from stuff), 0));
insert into stuff (name, position) VALUES ('seven', coalesce((select max(position) + 1 from stuff), 0));

select * from stuff order by position asc;


-- work with item with id 2.
-- moving UP the list by one place (position decrements). Will not go negative.
begin;
update stuff set position = position + 1 where position = (select position - 1 from stuff where id = 2);
update stuff set position = position - 1 where id = 2 and position > 0;
commit;

-- moving DOWN the list by one place (position increments). Will not increase beyond the number-of-items -1.
begin;
update stuff set position = position - 1 where position = (select position + 1 from stuff where id = 2);
update stuff set position = position + 1 where id = 2 and position < (select count(*) - 1 from stuff);
commit;

-- moving UP the list to a specific position (position decrements).
begin;
update stuff set position = position + 1 where position >= 1 -- the new position
    and position < (select position from stuff where id = 2);
update stuff set position = 1 -- the new position
    where id = 2;
commit;

-- moving DOWN the list to a specific position (position increments).
begin;
update stuff set position = position - 1 where position <= 4 -- the new position
    and position > (select position from stuff where id = 2);
update stuff set position = 4 -- the new position
    where id = 2;
commit;