Postgres 14

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.6 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-15), 64-bit



SELECT 1


create table test( fielda   text
                 , fieldb   integer
                 , uniqueid integer
                 ); 
  
create or replace function gen_unique_id()
  returns trigger 
 language plpgsql
as $$
begin 
    select coalesce( max(uniqueid)+1, 501)  
      into new.uniqueid
      from test 
     where fielda = new.fielda
       and fieldb = new.fieldb; 
   
    return new;
end; 
$$;

create trigger unique_id_biur
        before insert or update
            on test
          for each row
           execute function gen_unique_id();  

CREATE TABLE



CREATE FUNCTION



CREATE TRIGGER


insert into test(fielda, fieldb)
     values ('Zack',     928334)  
          , ('Yassir',   829384)  
          , ('Yassir',   829384)  
          , ('Zack',     928334)  
          , ('Zack',     928334)  
          , ('Richard',  723346); 

INSERT 0 6


select *
  from test; 


fielda
fieldb
uniqueid


Zack
928334
501

Yassir
829384
501

Yassir
829384
502

Zack
928334
502

Zack
928334
503

Richard
723346
501



SELECT 6


-- if leading 0 is required then use to_cher() with format '0000' 
-- (a 0 for each digit required)
select fielda, fieldb, to_char(uniqueid, '0000') uniqueid 
  from test; 


fielda
fieldb
uniqueid


Zack
928334
 0501

Yassir
829384
 0501

Yassir
829384
 0502

Zack
928334
 0502

Zack
928334
 0503

Richard
723346
 0501



SELECT 6

fielda	fieldb	uniqueid
Zack	928334	501
Yassir	829384	501
Yassir	829384	502
Zack	928334	502
Zack	928334	503
Richard	723346	501

fielda	fieldb	uniqueid
Zack	928334	0501
Yassir	829384	0501
Yassir	829384	0502
Zack	928334	0502
Zack	928334	0503
Richard	723346	0501