Learn Postgres in One Video (Full Notes)

docker run --name learn-postgres \
  -e POSTGRES_PASSWORD=postgres \
  -p 5432:5432 \
  -d postgres

docker exec -it learn-postgres psql -U postgres

create table users (
  id serial primary key,
  display_name text
);

insert into users (display_name) values ('Alice'), ('Bob');

select * from users where display_name like 'A%' order by id desc;

create extension if not exists pgcrypto;
 
-- Add new columns first — nullable for now, since the existing row (Alice) has no values yet
alter table users add column new_id uuid default gen_random_uuid();
alter table users add column email text;
alter table users add column age int;
alter table users add column created_at timestamptz not null default now();
 
-- Backfill a placeholder email so it can be safely made NOT NULL next
update users set email = lower(display_name) || '@example.com' where email is null;
 
-- Swap the primary key from the old integer id to the new uuid column
alter table users drop constraint users_pkey;
alter table users drop column id;
alter table users rename column new_id to id;
alter table users add primary key (id);
 
-- Now that every row has a value, these constraints can be safely enforced
alter table users alter column display_name set not null;
alter table users alter column email set not null;
alter table users add constraint users_email_key unique (email);
alter table users add constraint users_age_check check (age >= 13);

create table posts (
  id uuid primary key default gen_random_uuid(),
  user_id uuid not null references users(id) on delete cascade,
  title text not null,
  body text,
  status text not null default 'draft'
    check (status in ('draft', 'published', 'archived')),
  published_at timestamptz,
  metadata jsonb not null default '{}',
  created_at timestamptz not null default now()
);
 
create table comments (
  id uuid primary key default gen_random_uuid(),
  post_id uuid not null references posts(id) on delete cascade,
  user_id uuid not null references users(id) on delete cascade,
  body text not null,
  created_at timestamptz not null default now()
);

insert into users (email, display_name, age)
values ('kid@example.com', 'Too Young', 5);

insert into users (email, display_name, age) values
  ('alice@example.com', 'Alice', 29),
  ('bob@example.com', 'Bob', 34),
  ('carol@example.com', 'Carol', 22);
 
insert into posts (user_id, title, body, status, published_at, metadata)
select id, 'My First Post', 'Hello world!', 'published', now(),
  '{"featured": true, "reading_time_minutes": 2, "tags": ["intro", "hello-world"]}'
from users where email = 'alice@example.com';
 
insert into posts (user_id, title, body, status, published_at, metadata)
select id, 'Draft Thoughts', 'Not ready yet', 'draft', null, '{}'
from users where email = 'alice@example.com';
 
insert into posts (user_id, title, body, status, published_at, metadata)
select id, 'Bob''s Post', 'Some content', 'published', now(),
  '{"featured": false, "reading_time_minutes": 5, "tags": ["opinion"]}'
from users where email = 'bob@example.com';
 
-- Carol intentionally has zero posts — needed to demonstrate outer joins
 
insert into comments (post_id, user_id, body)
select p.id, u.id, 'Great post!'
from posts p, users u
where p.title = 'My First Post' and u.email = 'bob@example.com';
 
insert into comments (post_id, user_id, body)
select p.id, u.id, 'Thanks for sharing'
from posts p, users u
where p.title = 'My First Post' and u.email = 'carol@example.com';

select title, metadata->>'reading_time_minutes' as reading_time, metadata->'tags' as tags
from posts
where metadata @> '{"featured": true}';

select u.display_name, p.title
from users u
inner join posts p on p.user_id = u.id;

select u.display_name, p.title
from users u
left join posts p on p.user_id = u.id;

select u.display_name, p.title
from posts p
right join users u on p.user_id = u.id;

select u.display_name, p.title
from users u
full outer join posts p on p.user_id = u.id;

select u.display_name, s.status
from users u
cross join (values ('draft'), ('published'), ('archived')) as s(status);

select a.display_name, b.display_name, a.age
from users a
join users b on a.age = b.age and a.id < b.id;

select u.display_name, count(p.id) as post_count
from users u
left join posts p on p.user_id = u.id
group by u.display_name
order by post_count desc;

select u.display_name, count(p.id) as post_count
from users u
join posts p on p.user_id = u.id
group by u.display_name
having count(p.id) > 1;

select display_name, age
from users
where age > (select avg(age) from users);

select * from (
  select u.display_name, count(p.id) as published_count
  from users u
  join posts p on p.user_id = u.id and p.status = 'published'
  group by u.display_name
) as published_counts
where published_count >= 1;

select title from posts p
where exists (
  select 1 from comments c where c.post_id = p.id
);

with published_posts as (
  select * from posts where status = 'published'
),
comment_counts as (
  select post_id, count(*) as num_comments
  from comments
  group by post_id
)
select pp.title, coalesce(cc.num_comments, 0) as num_comments
from published_posts pp
left join comment_counts cc on cc.post_id = pp.id
order by num_comments desc;

begin;
 
update posts
set user_id = (select id from users where email = 'bob@example.com')
where title = 'My First Post';
 
insert into comments (post_id, user_id, body)
select id, user_id, 'Post reassigned to a new author'
from posts where title = 'My First Post';
 
commit;

begin;
delete from users where email = 'alice@example.com';
rollback;

select display_name, email from users order by display_name;

explain analyze
select * from posts
where user_id = (select id from users where email = 'alice@example.com');

create index idx_posts_user_id on posts(user_id);
 
explain analyze
select * from posts
where user_id = (select id from users where email = 'alice@example.com');

explain analyze
select * from posts where status = 'published' order by published_at desc;
 
create index idx_posts_status_published_at on posts(status, published_at desc);
 
explain analyze
select * from posts where status = 'published' order by published_at desc;

alter table posts enable row level security;
 
create policy "Published posts are public"
on posts for select
using (status = 'published');
 
create policy "Users can insert their own posts"
on posts for insert
with check (auth.uid() = user_id);
 
create policy "Users can modify their own posts"
on posts for update
using (auth.uid() = user_id);

alter publication supabase_realtime add table comments;

create policy "Users can upload their own avatars"
on storage.objects for insert
with check (
  bucket_id = 'avatars'
  and auth.uid()::text = (storage.foldername(name))[1]
);

// supabase/functions/notify-new-comment/index.ts
import { createClient } from 'jsr:@supabase/supabase-js@2'
 
Deno.serve(async (req) => {
  const { record } = await req.json()
 
  const supabase = createClient(
    Deno.env.get('SUPABASE_URL')!,
    Deno.env.get('SUPABASE_SERVICE_ROLE_KEY')!
  )
 
  const { data: post } = await supabase
    .from('posts')
    .select('title')
    .eq('id', record.post_id)
    .single()
 
  return new Response(JSON.stringify({ notified: post?.title }), {
    headers: { 'Content-Type': 'application/json' },
  })
})