By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
select version();
| version |
|---|
| PostgreSQL 13.4 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.4.1 20200928 (Red Hat 8.4.1-1), 64-bit |
-- setup
create table test(tid integer generated always as identity
primary key
, order_view integer not null
constraint test_order_view_uk
unique
, name text
);
insert into test ( order_view, name)
select n, 'Initial-' || to_char(n,'00')
from generate_series ( 1, 7) gs(n);
select * from test;
7 rows affected
| tid | order_view | name |
|---|---|---|
| 1 | 1 | Initial- 01 |
| 2 | 2 | Initial- 02 |
| 3 | 3 | Initial- 03 |
| 4 | 4 | Initial- 04 |
| 5 | 5 | Initial- 05 |
| 6 | 6 | Initial- 06 |
| 7 | 7 | Initial- 07 |
alter table test
drop constraint if exists test_order_view_uk;
alter table test
add constraint test_order_view_uk
unique(order_view)
deferrable initially deferred;
-- insert a new order_view 4
-- hard code demo values as fiddle in not interactive
with updt as
( update test
set order_view = order_view+1
where order_view >= 4
)
insert into test (order_view, name)
values (4, 'insert new 4');
select * from test;
1 rows affected
| tid | order_view | name |
|---|---|---|
| 1 | 1 | Initial- 01 |
| 2 | 2 | Initial- 02 |
| 3 | 3 | Initial- 03 |
| 8 | 4 | insert new 4 |
| 4 | 5 | Initial- 04 |
| 5 | 6 | Initial- 05 |
| 6 | 7 | Initial- 06 |
| 7 | 8 | Initial- 07 |
-- build Procedure to handle
create or replace
procedure insert_reorder_test(
name_in test.name%type
, order_view_in test.order_view%type default null
)
language sql
as $$
with new_order_view(nov) as
( select coalesce( order_view_in
, (select 1+coalesce(max(order_view),0)
from test
)
)
)
, updt as
( update test
set order_view = order_view+1
where order_view >= (select nov from new_order_view)
)
insert into test ( name, order_view)
select name_in
, nov
from new_order_view;
$$;
-- use function
do $$
begin
call insert_reorder_test('Create new #3',3);
call insert_reorder_test('Add to end of list',null);
end;
$$;
select * from test;
| tid | order_view | name |
|---|---|---|
| 1 | 1 | Initial- 01 |
| 2 | 2 | Initial- 02 |
| 9 | 3 | Create new #3 |
| 3 | 4 | Initial- 03 |
| 8 | 5 | insert new 4 |
| 4 | 6 | Initial- 04 |
| 5 | 7 | Initial- 05 |
| 6 | 8 | Initial- 06 |
| 7 | 9 | Initial- 07 |
| 10 | 10 | Add to end of list |