Skip to content

Instantly share code, notes, and snippets.

@swanandp
Created December 13, 2022 16:03
Show Gist options
  • Save swanandp/7383542d0b0188745838777c9478b394 to your computer and use it in GitHub Desktop.
Save swanandp/7383542d0b0188745838777c9478b394 to your computer and use it in GitHub Desktop.
Modelling "Friendships" in PostgreSQL

For this particular problem, of modelling mutual friendship what we truly need is a "SET" data type. But Postgres doesn't really have a set datatype. However, I realised that tsvectors are sorted sets and can be used for this. So, here's a fun solution with a single row: tsvectors

Our schema:

create table friendships (
  id bigint primary key,
  friends tsvector 
);

-- performance can be tested using either a GIN index or a BTREE index
create index fast_friends_lookup on friendships using btree (friends);

-- add constraint to keep tsvector length to 2
alter table friendships add constraint only_2_friends check (length(friends) = 2);

-- rows to test
insert into friendships values (1, to_tsvector('3 5'));
insert into friendships values (2, to_tsvector('3 7'));

List 3's friends:

select id, friends, tsvector_to_array(friends) from friendships where friends @@ '3'::tsquery;

Are 3 & 6 friends?

select id, friends, tsvector_to_array(friends) from friendships where friends = to_tsvector('3 6');
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment