Mastering Postgres
Introduction
Introduction to the course
Overview of course structure
Postgres vs. everyone
The psql CLI
Data Types
Introduction to schema
Integers
Numeric
Floating point
Storing money
NaNs and infinity
Casting types
Characters types
Check constraints
Domain types
Charsets and collations
Binary data
UUIDs
Boolean
Enums
Timestamps
Timezones
Dates and times
Advanced Data Types
Intervals
Serial type
Sequences
Identity
Network and mac addresses
JSON
Arrays
Generated columns
Text search types
Bit string
Ranges
Composite types
Nulls
Unique constraints
Exclusion constraints
Foreign key constraints
Indexing
Introduction to indexes
Heaps and CTIDs
B-Tree overview
Primary keys vs. secondary indexes
Primary key types
Where to add indexes
Index selectivity
Composite indexes
Composite range
Combining multiple indexes
Covering indexes
Partial indexes
Index ordering
Ordering nulls in indexes
Advanced Indexing
Functional indexes
Duplicate indexes
Hash indexes
Naming indexes
Understanding Query Plans
Introduction to explain
Explain structure
Scan nodes
Costs and rows
Explain analyze
Generating Results
Introduction to queries
Cross joins
Inner joins
Outer joins
Subqueries
Lateral joins
ROWS FROM
Filling gaps in sequences
Subquery elimination
Combining queries
Set generating functions
Indexing joins
Advanced SQL
Introduction to advanced SQL
Grouping
Grouping sets, rollups, cubes
Window functions
CTEs
CTEs with window functions
Recursive CTE
Hierarchical recursive CTE
Handling nulls
Row value syntax
Views
Materialized views
Removing duplicate rows
Upsert
Returning keyword
COALESCE + generated column
Full Text Search
Introduction to full text search
Searching with LIKE
Vectors, queries, and ranks
Websearch
Ranking
Indexing full text search
Highlighting
Advanced JSON
Intro to JSON
JSON vs JSONB
Validating JSON
Creating JSON objects + arrays
JSON extraction
JSON containment
JSON existence
JSON recordset
Updating JSON
Indexing JSON parts
GIN index
Vectors (pgvector)
Intro to pgvector
Vector embedding columns
Find related articles
Upsert vector embedding
Semantic search
Other operators
Vector indexes
Outro
Thank you
Bonus interviews
Heroku's glory days & Postgres vs the world (with Craig Kerstiens)
Creating a Postgres platform with Monica & Tudor from Xata.io
Bootstrapping an email service provider (with Jesse Hanley)
Locked video

Please purchase the course to watch this video.

Video thumbnail
Advanced Data Types
Nulls

Full Course

$
349
$399
USD, one-time fee
The course broke down complex subjects into easy-to-understand videos that I've referred back to multiple times since completing it. It strengthened my knowledge by filling gaps and expanding into new areas I hadn't known about—absolutely worth doing!
Lawrence
Lawrence

PostgreSQL database platform

Shorten dev cycles with branching and zero-downtime schema migrations.

Test Data

I've made my test data available for you to use and follow along.

Download
or
Use on Xata

Summary

Explore how the `NOT NULL` constraint improves database reliability by ensuring that columns always contain a value. You'll learn why avoiding nulls helps with indexing, query performance, and data consistency. Practical examples demonstrate how to apply `NOT NULL` constraints effectively across different types of columns.

Video Transcript

We talked about check constraints several videos ago, because it was relevant to whatever we were doing back then. I want to talk about a few more constraints, starting with not null, and this will round out the end of this module. I don't know if that's exciting for you.

We've done a lot of data types, but before we look at not null constraints, I do want to remind you just quickly that nulls are weird. Nulls are weird. Every database, you'll run into this when you're comparing nulls, and we're gonna look at this in the querying section even more. I just want to remind you, nulls are unknown values. They're not zero, they're not false, they're not empty strings, they're not empty. It's just simply unknown. When you ask the database, "Does the number one equal this secret thing that I'm holding in my hand?" the database has to respond, "I don't know. What's the secret thing you're holding in your hand? What is this null value? It is unknown, it is unknowable. I cannot compare those two things."

You can say, "Is a column null?" It will say, "Yes, that is null. Thank you for asking." If you say, "Does the string Aaron equal this secret thing that I'm hiding from you?" It must respond, "I do not know," which is a null value. Nulls are a little bit weird.

We will look at the comparisons, the three-valued Boolean logic. We'll look at all of that stuff a little bit further down the line. My last charge to you before we look at some code here is most of the time you want to constrain your columns to be not null. If you can, if your data model allows for it, you want to make your columns not null, you're gonna get a lot of niceties, a lot of nice things out of that, not least of which is enforcement that there must be a value there. When it comes to indexing, and comparing, and grouping, and sorting, and querying, everything just becomes a lot nicer. Opt for the default being not null. If you know that a column is nullable, then of course you can allow it to be nullable. Don't make up your own version of null just to satisfy what some guy told you that you should make it not nullable. Make it not null if it's not null, but otherwise it's fine, just move on.

Let's look at some code. We're gonna start at the top here. We've got id bigint generated always as identity primary key. This primary key adds a not null constraint on this column because a primary key cannot be null. You're good to go already there. Now by default, these are nullable. If we want to make these not null, we have to add a constraint here. You could write it like this. I don't recommend this, so don't pause. Don't pause the video and go do this, check (name is not null). You could do that and it would totally work. It's just not recommended.

Let's drop table products and come back up here, and let's do it the right way. You can do it this way, you can do it this way. It is not as efficient as it could be. We'd just say text not null. There you go. We have created the table.

Let's say that we want price to be not null as well. You can also tack on another check constraint like we talked about several videos ago. You can say, in fact, the price must also be greater than zero. Now, you have both not null and a little bit of domain logic to say, well, zero is not that different than null for our business. Let's make sure that it's greater than zero as well. That's it for nulls.

You can just declare a column as not null, which I think should be your preferred method of operating. Make every column not null unless you have a very good reason to make it nullable. Write it in the not null format when not using a check constraint, but you can add a check constraint onto the end.