add batch remove batch split batch comment selection show hidden batches hide batch highlight batch
db<>fiddle
donate feedback about
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 2024-06-26
2 2 5 P 2024-06-26
3 3 4 P 2024-06-26
4 5 1 P 2024-06-26 
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, 2024-06-26).
-- 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 2024-06-26
1 1 3 P 2024-06-26
9 1 4 P 2024-06-26
12 2 3 P 2024-06-26
13 2 4 P 2024-06-26
2 2 5 P 2024-06-26
3 3 4 P 2024-06-26
18 3 5 P 2024-06-26
22 4 5 P 2024-06-26
4 5 1 P 2024-06-26 
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
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