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



SELECT 1


create table users (user_id integer generated always as identity 
                   ,name text not null
                   ,constraint users_pk primary key (user_id)
                   ,constraint users_bk unique (name)
                   );
--                  
insert into users(name) 
     values ('Sam')
          , ('Linda')
          , ('George')
          , ('Tom') 
          , ('Ritta');   
select * from users;  

CREATE TABLE



INSERT 0 5




user_id
name


1
Sam

2
Linda

3
George

4
Tom

5
Ritta



SELECT 5


create table friends( f_id integer generated always as identity
                    , user_id_1 integer  not null 
                    , user_id_2 integer  not null
                    , status  varchar(1)   not null default 'P' 
                    , friends_since     date  not null default now()::date  
                    , constraint friends_pk primary key(f_id)
                    , constraint f2user_1_fk foreign key(user_id_1)
                                 references users(user_id)
                    , constraint f2user_2_fk foreign key(user_id_2)
                                 references users(user_id) 
                    , constraint cannot_friend_self
                                 check (user_id_1 != user_id_2)
                    );
--
-- unique index to reconize (P1,P2) = (P2,P1)                  
create unique index already_friends on friends  
        ( least(user_id_1,user_id_2), greatest(user_id_1,user_id_2) ); 

CREATE TABLE



CREATE INDEX


insert into friends(user_id_1,user_id_2) 
     values (1,3), (2,5),(3,4),(5,1);
select * from friends;  

INSERT 0 4




f_id
user_id_1
user_id_2
status
friends_since


1
1
3
P
2023-11-27

2
2
5
P
2023-11-27

3
3
4
P
2023-11-27

4
5
1
P
2023-11-27



SELECT 4


-- validate (P1,P2) = (P2,P1). Note (5,1) already exists 
-- so validate that new values (1,5) = existing (5,1)
insert into friends(user_id_1,user_id_2) values(1,5);

ERROR:  duplicate key value violates unique constraint "already_friends"
DETAIL:  Key (LEAST(user_id_1, user_id_2), GREATEST(user_id_1, user_id_2))=(1, 5) already exists.


-- I am my friend
insert into friends(user_id_1,user_id_2) values(3,3);

ERROR:  new row for relation "friends" violates check constraint "cannot_friend_self"
DETAIL:  Failing row contains (6, 3, 3, P, 2023-11-27).


-- make everybody friends
insert into friends(user_id_1, user_id_2)
  select u1.user_id, u2.user_id 
    from users u1 
    join users u2  on u2.user_id != u1.user_id
 on conflict  ( least(user_id_1,user_id_2), greatest(user_id_1,user_id_2) )
 do nothing;
select * from friends order by user_id_1, user_id_2;

INSERT 0 6




f_id
user_id_1
user_id_2
status
friends_since


7
1
2
P
2023-11-27

1
1
3
P
2023-11-27

9
1
4
P
2023-11-27

12
2
3
P
2023-11-27

13
2
4
P
2023-11-27

2
2
5
P
2023-11-27

3
3
4
P
2023-11-27

18
3
5
P
2023-11-27

22
4
5
P
2023-11-27

4
5
1
P
2023-11-27



SELECT 10


-- new users
insert into users(name) values('Donald'),('Vladimir');

INSERT 0 2


-- some useful functions
create or replace 
function show_users_with_no_friends()
 returns table ( user_name_in   users.name%type 
               , friend_name_in users.name%type 
               )
  language sql
as $$
    select u.name, null::text
      from users u 
     where not exists 
           ( select null 
               from friends f 
              where u.user_id in (f.user_id_1,f.user_id_2)
           ) ; 
$$;  
-----------------------------------------------------------
create or replace 
function list_friends(user_name_in users.name%type)
  returns table (user_name    users.name%type
                ,friends_name users.name%type
                )
  language sql strict
as $$
    with fr(uid, f1,f2) as
         (select u.user_id ,f.user_id_1,f.user_id_2
            from users   u 
            join friends f on u.user_id in (f.user_id_1,user_id_2)   
            where u.name = user_name_in)
    select case when uid = f1 
                then u1.name 
                else u2.name
           end 
         , case when uid = f2 
                then u1.name 
                else u2.name
           end       
      from fr 
      join users u1 on u1.user_id = fr.f1
      join users u2 on u2.user_id = fr.f2;
$$;  
-----------------------------------------------------------
create or replace 
function make_friends(user_name_in   users.name%type  
                     ,friend_name_in users.name%type 
                     )
   returns void
 language sql
as $$
    insert into friends (user_id_1, user_id_2)
       with u(user_id) as
             (select  user_id from users where  name = user_name_in)
          , f(user_id) as
             (select  user_id from users where  name = friend_name_in) 
       select u.user_id, f.user_id
         from u cross join f
       ;
$$; 
 --

CREATE FUNCTION



CREATE FUNCTION



CREATE FUNCTION


-- who is lonely 
select * from show_users_with_no_friends(); 


user_name_in
friend_name_in


Donald
null

Vladimir
null



SELECT 2


select make_friends('Donald','Vladimir');


make_friends


null



SELECT 1


select * from list_friends('Donald');
select * from list_friends('Vladimir');
select * from list_friends('Sam') order by friends_name;


user_name
friends_name


Donald
Vladimir



SELECT 1




user_name
friends_name


Vladimir
Donald



SELECT 1




user_name
friends_name


Sam
George

Sam
Linda

Sam
Ritta

Sam
Tom



SELECT 4
f_id	user_id_1	user_id_2	status	friends_since
7	1	2	P	2023-11-27
1	1	3	P	2023-11-27
9	1	4	P	2023-11-27
12	2	3	P	2023-11-27
13	2	4	P	2023-11-27
2	2	5	P	2023-11-27
3	3	4	P	2023-11-27
18	3	5	P	2023-11-27
22	4	5	P	2023-11-27
4	5	1	P	2023-11-27