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 14.10 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-20), 64-bit |
SELECT 1
-- setup
create table banks( id integer generated always as identity
, bank_number integer
);
insert into banks(bank_number)
values (13),(37),(43);
CREATE TABLE
INSERT 0 3
create sequence invoice_nummer_seq_tn_13 start with 317 minvalue 0;
create sequence invoice_nummer_seq_tn_37 start with 678 minvalue 0;
create sequence invoice_nummer_seq_tn_43 start with 939 minvalue 0;
CREATE SEQUENCE
CREATE SEQUENCE
CREATE SEQUENCE
-- show current sequence value
select 'invoice_nummer_seq_tn_13' "seq", nextval('invoice_nummer_seq_tn_13') "next val"
union all
select 'invoice_nummer_seq_tn_37', nextval('invoice_nummer_seq_tn_37')
union all
select 'invoice_nummer_seq_tn_43', nextval('invoice_nummer_seq_tn_43') ;
| seq | next val |
|---|---|
| invoice_nummer_seq_tn_13 | 317 |
| invoice_nummer_seq_tn_37 | 678 |
| invoice_nummer_seq_tn_43 | 939 |
SELECT 3
-- Block to reset sequence
do $$
declare
bn int4;
stmt text;
base constant text = 'alter sequence invoice_nummer_seq_tn_%s restart with 0';
begin
for bn in (select bank_number from banks b)
loop
stmt = format(base,bn::text);
raise notice E'---Running Statement:---\n\t%\n------',stmt;
execute stmt;
end loop;
end;
$$;
DO
-- show current sequence value
select 'invoice_nummer_seq_tn_13' "seq", nextval('invoice_nummer_seq_tn_13') "next val"
union all
select 'invoice_nummer_seq_tn_37', nextval('invoice_nummer_seq_tn_37')
union all
select 'invoice_nummer_seq_tn_43', nextval('invoice_nummer_seq_tn_43') ;
| seq | next val |
|---|---|
| invoice_nummer_seq_tn_13 | 0 |
| invoice_nummer_seq_tn_37 | 0 |
| invoice_nummer_seq_tn_43 | 0 |
SELECT 3