Francesco Tisiot: 11 Lessons to learn when using NULLs in PostgreSQL®

A boolean value should only contain two values, True or False, but is it correct? Usually people assume so, but sometimes miss the fact that there could be the absence of the value all-together. In databases this is absence is usually stored as NULL and this blog showcases how to find them, use them properly and 11 lessons to learn to be a NULL Pro! Keep in mind, it's not only booleans that can contain NULL values, it's all the columns where you don't define a NOT NULL constraint! If you need a FREE PostgreSQL database? 🦀 Check Aiven's FREE plans! 🦀 If you need to optimize your SQL query? 🐧 Check EverSQL! 🐧 It all starts with some columns and rows Let's start from the basics: you have a PostgreSQL® database and a table, called users like: CREATE TABLE users ( id SERIAL, username TEXT PRIMARY KEY, name TEXT, surname TEXT, age INT ); Hey, do you want to test the above code but not having a PostgreSQL database handy? Past your code in PostgreSQL Playground and quickly check the results! Let's insert some data: INSERT INTO users (username, name, surname, age) VALUES ('jdoe', 'Jon', 'Doe', 25), ('lspencer','Liz', 'Spencer', 35), ('hlondon','Hanna', 'London', 45); Querying the data showcases the table with all the columns filled. id | username | name | surname | age ----+----------+-------+---------+----- 1 | jdoe | Jon | Doe | 25 2 | lspencer | Liz | Spencer | 35 3 | hlondon | Hanna | London | 45 (3 rows) Insert NULLs Now, let's check if we can insert some NULLs, let's try by inserting them in the name, surname and age columns: INSERT INTO users (username, name, surname, age) VALUES ('test',NULL, NULL, NULL); This works since we don't have any constraint id | username | name | surname | age ----+----------+-------+---------+----- 1 | jdoe | Jon | Doe | 25 2 | lspencer | Liz | Spencer | 35 3 | hlondon | Hanna | London | 45 4 | test | [...]