Learn Postgres in One Video (Full Notes)

Full written notes to follow along with the video, or come back to as a reference later. Every query below is runnable: copy it straight into Supabase's SQL Editor, psql, or pgAdmin.

Environment setup

Two ways to follow along:

Option A, Supabase (hosted). No local install. Create a free project at Supabase, open the SQL Editor, and you're using standard Postgres SQL. Nothing Supabase-specific until the back half of this guide.

Option B, local Postgres via Docker.

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

docker exec -it learn-postgres psql -U postgres

Everything through the Indexes section works identically either way. Only the Supabase-specific features (Auth/RLS, Realtime, Storage, Edge Functions) require an actual Supabase project.

Local GUI with pgAdmin. psql alone has no visual table grid. Want one instead of the terminal? pgAdmin is the most common free Postgres GUI, runs as its own Docker container, and gives a Table Editor / query results grid comparable to Supabase's Studio.

# Runs pgAdmin as a second container on the same Docker network as "learn-postgres"
# so it can reach the database by container name instead of an IP address
docker network create pg-net
docker network connect pg-net learn-postgres
 
docker run --name pgadmin \
  -e PGADMIN_DEFAULT_EMAIL=admin@example.com \
  -e PGADMIN_DEFAULT_PASSWORD=admin \
  -p 5050:80 \
  --network pg-net \
  -d dpage/pgadmin4

Open http://localhost:5050, log in with the email/password above, then Add New Server: Name = anything, Host = learn-postgres (the container name, resolved via the shared Docker network), Port = 5432, Username = postgres, Password = postgres (from the earlier docker run). Once connected, pgAdmin's Query Tool and table browser behave the same way as Supabase's SQL Editor / Table Editor for everything in this guide.

To stop and remove both containers when done:

docker stop learn-postgres pgadmin && docker rm learn-postgres pgadmin && docker network rm pg-net

SQL basics recap

A fast refresher before we build the real schema. The running example throughout this guide is a tiny blog: users, posts, comments.

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

CREATE TABLE

insert into users (display_name) values ('Piyush'), ('Ronny');

INSERT 0 2

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

This users table is deliberately bare: no constraints, and id isn't even the right type yet. The next section rebuilds it for real, evolving it in place rather than dropping it, since that's what actually happens to schemas once they have live data.

Data types & constraints

Constraints are where bad data gets stopped at the database level, not just in application code. We'll also switch to uuid primary keys instead of serial integers, which work better for public-facing IDs and multi-service setups.

Evolving the table with ALTER TABLE

create extension if not exists pgcrypto;
 
-- Add new columns first — nullable for now, since the existing row (Piyush) 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 EXTENSION
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
UPDATE 1
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE

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()
);

CREATE TABLE
CREATE TABLE

Without on delete cascade, deleting a referenced user would throw a foreign key violation instead of cascading. That's a design decision made at table-creation time, which is why posts and comments are created fresh rather than evolved with ALTER TABLE.

Here's the full schema now that all three tables exist, and how they connect. (Open in Eraser for an interactive version.)

jsonb: Postgres's binary JSON type

posts.metadata is jsonb, the type that separates Postgres from most relational databases. Use structured columns for data you know the shape of (title, status), and jsonb for data whose shape varies or isn't worth a migration for every new field (tags, SEO metadata, per-post settings).

jsonb vs json: jsonb stores data in parsed binary format. That's slightly slower to insert, much faster to query, and indexable. Use jsonb unless you specifically need to preserve exact key order.

Proving the CHECK constraint works

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

ERROR:  new row for relation "users" violates check constraint "users_age_check"
DETAIL:  Failing row contains (..., kid@example.com, Too Young, 5, ...).

Bad data cannot enter the table, regardless of what the application layer does.

Seed data

insert into users (email, display_name, age) values
  ('piyush@example.com', 'Piyush', 27),
  ('ronnysins@example.com', 'Ronny', 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 = 'piyush@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 = 'piyush@example.com';
 
insert into posts (user_id, title, body, status, published_at, metadata)
select id, 'Ronny''s Post', 'Some content', 'published', now(),
  '{"featured": false, "reading_time_minutes": 5, "tags": ["opinion"]}'
from users where email = 'ronnysins@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 = 'ronnysins@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';

INSERT 0 3
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1

Carol has a comment but no posts. That asymmetry is what makes the join section below meaningful.

Querying the jsonb column

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

-> returns a JSON value (stays jsonb), ->> returns it as text, and @> checks containment: "does this jsonb contain this jsonb." Only "My First Post" comes back since it's the only one with featured: true.

Relationships & every join type

Joins are about which rows survive the match, not syntax to memorize. Picture two circles, users and posts, and each join type is a different answer to "which parts of the overlap and leftovers do I want?" Carol has no matching row in posts. That's the gap every join below either fills or drops. Each preview below shows the same users/posts rows from Seed Data, with a dot marking whether a row is kept (matched), kept with NULLs, or dropped entirely.

INNER JOIN

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

Carol is absent entirely, since she has no matching post row. Inner join is the most common default, and also the #1 cause of "missing data" bugs when a left join was actually needed.

LEFT (OUTER) JOIN

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

Carol now appears, with NULL for title. This is the join you want for "show me everyone, plus their related data if any exists."

RIGHT JOIN

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

Same result as the LEFT JOIN, just with table order flipped. Rarely used in practice; most people just reorder tables and use LEFT JOIN instead.

FULL OUTER JOIN

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

A FULL OUTER JOIN only differs from LEFT when the right side also has unmatched rows. It's useful for reconciliation queries where you want unmatched rows from both sides at once.

CROSS JOIN

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

Every combination of every row, no ON clause. 3 users × 3 statuses = 9 rows. Useful for generating report matrices; rarely intended by accident.

Self join

Piyush (27), Ronny (34), and Carol (22) all have different ages right now, so a self join wouldn't return any pairs. To see a real match without adding a throwaway row, temporarily set Piyush's age to match Carol's:

update users set age = 22 where email = 'piyush@example.com';
 
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;

UPDATE 1

display_name | display_name | age
---------------+---------------+-----
Piyush        | Carol         |  22
(1 row)

Piyush and Carol now share age = 22, so they match. The a.id < b.id condition avoids self-matches and duplicate mirrored pairs (it's what keeps this from also returning the reverse "Carol, Piyush" row).

Revert before continuing, so the rest of this guide's output matches the original seed data:

update users set age = 27 where email = 'piyush@example.com';

UPDATE 1

Aggregations & subqueries

GROUP BY / HAVING

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;

count(p.id) counts non-null values only, so Carol correctly gets 0, not 1.

select u.display_name, u.age
from users u
join posts p on p.user_id = u.id
where u.age > 25;

WHERE runs first, filtering individual joined rows before any grouping happens — Carol (22) is excluded here, before count() even enters the picture. Now chain that same filter into a grouped, aggregated query:

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

WHERE filters rows before grouping (drops Carol early); HAVING filters groups after aggregation (drops Ronny, whose single post doesn't clear count(p.id) > 1). You can't write WHERE count(*) > 1, since COUNT doesn't exist yet at the WHERE stage — that's exactly why HAVING exists as a separate clause.

Subqueries

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;

A subquery in FROM (a derived table). Piyush's "Draft Thoughts" doesn't count, since it's status = 'draft', not 'published'.

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

     title
----------------
My First Post
(1 row)

EXISTS stops scanning at the first match, which is often faster than IN on large tables.

CTEs (Common Table Expressions)

CTEs are named subqueries using WITH. They make complex queries readable, and in Postgres they can also recurse.

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;

"Draft Thoughts" is excluded, since it's not in the published_posts CTE (its status = 'draft').

with recursive comment_thread as (
  select id, post_id, body, parent_id, 1 as depth
  from comments
  where parent_id is null
  union all
  select c.id, c.post_id, c.body, c.parent_id, ct.depth + 1
  from comments c
  join comment_thread ct on c.parent_id = ct.id
)
select * from comment_thread order by depth;

Transactions

Multiple writes that must succeed or fail together need a transaction.

-- Everything between BEGIN and COMMIT is one atomic unit: both writes land, or neither does
begin;
 
update posts
set user_id = (select id from users where email = 'ronnysins@example.com')
where title = 'My First Post';
 
-- Logging the reassignment in the same transaction — if this insert failed, the update above
-- would never actually commit either
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
UPDATE 1
INSERT 0 1
COMMIT

begin;
delete from users where email = 'piyush@example.com'; -- takes effect only inside this transaction
rollback; -- discards everything since BEGIN — the delete never actually happened

BEGIN
DELETE 1
ROLLBACK

select display_name, email from users order by display_name;

Piyush is still here. The DELETE inside the transaction never actually took effect, because it was rolled back. This is atomicity, one of Postgres's ACID guarantees: without transactions, a crash mid-sequence leaves data half-written.

Indexes & EXPLAIN ANALYZE

Don't just take "add an index" on faith. Here's how to prove it helped.

-- No index on posts.user_id yet — EXPLAIN ANALYZE shows how Postgres actually finds these rows
explain analyze
select * from posts
where user_id = (select id from users where email = 'piyush@example.com');

 Seq Scan on posts  (cost=0.05..1.09 rows=1 width=...) (actual time=0.020..0.023 rows=1 loops=1)
 Filter: (user_id = $0)
 InitPlan 1 (returns $0)
   ->  Seq Scan on users  (cost=0.00..1.04 rows=1 width=16) (actual time=0.008..0.009 rows=1 loops=1)
         Filter: (email = 'piyush@example.com'::text)
Planning Time: 0.15 ms
Execution Time: 0.04 ms

The plan executes bottom-to-top. Seq Scan means Postgres read every row to find matches, which is fine on a tiny table but costly at scale. Two numbers matter most: cost (the planner's own estimate) and actual time (real milliseconds, what users feel).

-- Same query as above, now with an index the planner is free to use
create index idx_posts_user_id on posts(user_id);
 
explain analyze
select * from posts
where user_id = (select id from users where email = 'piyush@example.com');

 Seq Scan on posts  (cost=0.05..1.09 rows=1 width=...) (actual time=0.018..0.021 rows=1 loops=1)
 Filter: (user_id = $0)
 InitPlan 1 (returns $0)
   ->  Seq Scan on users  (cost=0.00..1.04 rows=1 width=16) (actual time=0.007..0.008 rows=1 loops=1)
         Filter: (email = 'piyush@example.com'::text)
Planning Time: 0.14 ms
Execution Time: 0.03 ms

On a tiny table, the planner may still choose a sequential scan. That's correct behavior, not a bug. Indexes prove their value at scale and with selective filters, not on 6 rows. Seed thousands of rows instead and this flips to Index Scan using idx_posts_user_id.

-- Before: filtering by status still needs a sort step to satisfy ORDER BY
explain analyze
select * from posts where status = 'published' order by published_at desc;
 
-- Composite index matches both the WHERE column and the ORDER BY column/direction,
-- so rows can come back pre-sorted straight from the index
create index idx_posts_status_published_at on posts(status, published_at desc);
 
-- After: same query, no more explicit Sort node
explain analyze
select * from posts where status = 'published' order by published_at desc;

 Sort  (cost=1.10..1.11 rows=2 width=...) (actual time=0.025..0.026 rows=2 loops=1)
 Sort Key: published_at DESC
 ->  Seq Scan on posts  (cost=0.00..1.09 rows=2 width=...) (actual time=0.010..0.012 rows=2 loops=1)
       Filter: (status = 'published'::text)
Planning Time: 0.10 ms
Execution Time: 0.04 ms

 Seq Scan on posts  (cost=0.00..1.09 rows=2 width=...) (actual time=0.009..0.011 rows=2 loops=1)
 Filter: (status = 'published'::text)
Planning Time: 0.12 ms
Execution Time: 0.03 ms

The explicit Sort node disappears, since the index already returns rows pre-sorted. At real scale this also flips the scan itself to an Index Scan.

What Supabase adds on top

Everything above (data types, constraints, joins, aggregations, subqueries, CTEs, transactions, indexes) is stock Postgres. It works identically self-hosted, on RDS, or anywhere else. Supabase is a hosted Postgres database with extra services layered on top; here's exactly where that line sits.

Auth + Row Level Security

Supabase's hosted auth service matters, but the interesting mechanic underneath is Row Level Security (RLS) — a native Postgres feature Supabase wires up by default. Auth itself is small enough to read in full: a couple of supabase-js calls, no separate auth library. An AI coding tool like Claude Code can scaffold the whole thing from one prompt:

Init a new React + TypeScript app. Install @supabase/supabase-js. Add a simple email/password sign-up/sign-in form (plain HTML inputs, no UI library) that calls supabase.auth.signUp() and supabase.auth.signInWithPassword(). Add a header with the app name on the left and, once signed in, a user menu on the right showing the email with a sign-out button that calls supabase.auth.signOut().

Before running it, grab the Project URL and anon public key from Supabase's dashboard (Settings → API) and put them in a .env file — the generated Supabase client reads these to know which project to talk to:

VITE_SUPABASE_URL=https://your-project-ref.supabase.co
VITE_SUPABASE_ANON_KEY=your-anon-key

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 TABLE
CREATE POLICY
CREATE POLICY
CREATE POLICY

With those policies in place, one more prompt is enough to prove RLS works visually — this one exercises all three policies above, not just select. First, add posts to Supabase's realtime publication so clients can subscribe to changes on it (more on what this does in Realtime below):

alter publication supabase_realtime add table posts;

ALTER PUBLICATION

Add a Posts page that lists all posts (title + status) using supabase-js, and link to it from the header. If the user is signed in, also show a small form on that page to create a new post (title + body). The list should update in realtime when a post is added — subscribe to inserts on the posts table instead of refetching.

The list is a plain .from('posts').select() call, the create form is a plain .from('posts').insert() call, and the live-update part is a supabase.channel(...).on('postgres_changes', ...) subscription — no auth-specific or polling code written by hand anywhere. Signed out, only status = 'published' posts come back and there's no create form, per the select policy. Signed in, submitting the form inserts a row owned by the current user (the insert policy firing for real, not just described) — open the page in two browser tabs and that new row appears in both within a second or two, with no refresh.

Realtime

The posts subscription above is the concrete version of what this section is about: Supabase's realtime layer, built on Postgres logical replication (LISTEN/NOTIFY, replication slots) rather than anything client-specific.

alter publication supabase_realtime add table comments;

ALTER PUBLICATION

Storage

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]
);

CREATE POLICY

This is a single-page overview, not a deep dive — Storage really clicks once it's wired into a real app (uploads, transformations, serving). Full Stack React Project (Agentic App Builder) — Next.js, Supabase, TypeScript builds it into a working feature end to end.

Edge Functions

// 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' },
  })
})

{"notified":"My First Post"}

Same caveat as Storage: this is a one-shot demo of the concept. The same Full Stack React Project (Agentic App Builder) — Next.js, Supabase, TypeScript video puts Edge Functions to work on a real feature, which is the best way to actually learn them hands-on.