Postgres 12

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 12.4 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.3.1 20191121 (Red Hat 8.3.1-5), 64-bit


-- setup (for later)
create table puskesmas(pus_code varchar(7) primary key);
insert into puskesmas(pus_code) values  ('A'),('B'),('C');
3 rows affected

-- chat_room (this will be the only table)
create table chat_room
(
    id  integer generated always as identity
        constraint chat_room_pk
            primary key, 
    name varchar(255) not null,
    description varchar(255),
    profile_pic varchar(128),
    is_private boolean not null default false,
    is_group boolean not null default false,
    pus_code varchar(7) 
        constraint fk__group_chat_room__puskesmas
            references puskesmas
                on update cascade on delete cascade,        
    created_at timestamp with time zone default now() not null,
    updated_at timestamp with time zone,
    constraint not_group_of_pus_code_check
               check (   (not is_group and pus_code is null)
                      or (is_group and pus_code is not null)
                     ) 
);

-- create unique partial index
create unique index group_chat_room_pus_code_uindex on chat_room(pus_code)
    where is_group;
-- group_chat_room
create view group_chat_room 
       ( id   
       , name  
       , description 
       , profile_pic  
       , is_private  
       , pus_code 
       , created_at  
       , updated_at
       ) as 
  select id             
       , name           
       , description    
       , profile_pic    
       , is_private   
       , pus_code       
       , created_at     
       , updated_at 
   from chat_room
  where is_group;
-- private_chat_room
create view private_chat_room                    
       ( id                                     
       , name                                   
       , description                            
       , profile_pic                            
       , is_group                             
       , pus_code                               
       , created_at                             
       , updated_at                             
       ) as                                     
  select id                                     
       , name                                   
       , description                            
       , profile_pic                           
       , is_group                            
       , pus_code                                  
       , created_at                            
       , updated_at       
   from chat_room
  where is_private;
  
-- test valid inserts  
insert into chat_room(name, description)
     values ('Standard 1', 'Neither private nor group');    
insert into chat_room( name, description,is_private)
     values ('Private 1', 'A private chat',true);
insert into chat_room( name, description,is_group,pus_code)
     values ('Group 1', 'A group chat',true,'A');
insert into chat_room( name, description,is_private,is_group,pus_code)
     values ('Private Group 1', 'Strange but notg Invalid',true,true,'B'); 
1 rows affected

1 rows affected

1 rows affected

1 rows affected

select * from chat_room;
select * from group_chat_room;
select * from private_chat_room;


id
name
description
profile_pic
is_private
is_group
pus_code
created_at
updated_at


1
Standard 1
Neither private nor group
null
f
f
null
2020-08-30 23:04:19.492607+01
null

2
Private 1
A private chat
null
t
f
null
2020-08-30 23:04:19.492607+01
null

3
Group 1
A group chat
null
f
t
A
2020-08-30 23:04:19.492607+01
null

4
Private Group 1
Strange but notg Invalid
null
t
t
B
2020-08-30 23:04:19.492607+01
null




id
name
description
profile_pic
is_private
pus_code
created_at
updated_at


3
Group 1
A group chat
null
f
A
2020-08-30 23:04:19.492607+01
null

4
Private Group 1
Strange but notg Invalid
null
t
B
2020-08-30 23:04:19.492607+01
null




id
name
description
profile_pic
is_group
pus_code
created_at
updated_at


2
Private 1
A private chat
null
f
null
2020-08-30 23:04:19.492607+01
null

4
Private Group 1
Strange but notg Invalid
null
t
B
2020-08-30 23:04:19.492607+01
null


-- test constraints
insert into chat_room( name, description,is_group,pus_code)
     values ('Invalid group 1', 'no such pus_code', true,'X'); 
ERROR:  insert or update on table "chat_room" violates foreign key constraint "fk__group_chat_room__puskesmas"
DETAIL:  Key (pus_code)=(X) is not present in table "puskesmas".


insert into chat_room( name, description,is_group )
     values ('Invalid group 2', 'Missing pus_code', true);     
ERROR:  new row for relation "chat_room" violates check constraint "not_group_of_pus_code_check"
DETAIL:  Failing row contains (6, Invalid group 2, Missing pus_code, null, f, t, null, 2020-08-30 23:04:19.495784+01, null).


insert into chat_room( name, description,pus_code )
     values ('Invalid group 3', 'pus_code but not is_group', 'C');
ERROR:  new row for relation "chat_room" violates check constraint "not_group_of_pus_code_check"
DETAIL:  Failing row contains (7, Invalid group 3, pus_code but not is_group, null, f, f, C, 2020-08-30 23:04:19.49601+01, null).


insert into chat_room( name, description,is_group,pus_code )
     values ('Invalid group 4', 'pus_code already exists', true,'A');
ERROR:  duplicate key value violates unique constraint "group_chat_room_pus_code_uindex"
DETAIL:  Key (pus_code)=(A) already exists.

id	name	description	profile_pic	is_private	is_group	pus_code	created_at	updated_at
1	Standard 1	Neither private nor group	null	f	f	null	2020-08-30 23:04:19.492607+01	null
2	Private 1	A private chat	null	t	f	null	2020-08-30 23:04:19.492607+01	null
3	Group 1	A group chat	null	f	t	A	2020-08-30 23:04:19.492607+01	null
4	Private Group 1	Strange but notg Invalid	null	t	t	B	2020-08-30 23:04:19.492607+01	null