Data Types and NULL

Relax. We'll talk through this in plain words — no textbook voice.

What you'll walk away with

Explain the core ideas behind Data Types and NULL
Run the sample SQL or command and verify its output
Apply the technique to the Tutorial Platform and production scenarios

Build the mental model

A data type defines valid values, available operations, and part of the database's validation contract. Use `numeric` rather than floating point for money, and prefer `timestamptz` for real-world events. `NULL` is neither an empty string nor zero; it represents unknown or absent information and introduces three-valued logic.

Connect it to a real scenario

Store a tutorial title as `text`, price as `numeric(10,2)`, publication state as boolean, and flexible metadata as `jsonb`. Leave `published_at` as `NULL` before publication and test it with `IS NULL`, not equality.

Choose types by value semantics
Use `timestamptz` for event times
Test NULL with `IS NULL` / `IS NOT NULL`

Try the working example

sql

CREATE TABLE app.tutorials (
  tutorial_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  title text NOT NULL,
  slug text NOT NULL UNIQUE,
  price numeric(10,2) NOT NULL DEFAULT 0 CHECK (price >= 0),
  is_published boolean NOT NULL DEFAULT false,
  published_at timestamptz,
  metadata jsonb NOT NULL DEFAULT '{}'::jsonb
);

SELECT title FROM app.tutorials WHERE published_at IS NULL;

You should see

You have an `app.tutorials` table whose column types match their domain meaning.

5-minute try-it

Choose types for lesson duration, rating, an optional video URL, and publication time, then justify each choice.

One important caution

`WHERE published_at = NULL` never becomes true. Use `IS NULL` for NULL checks.

PostgreSQL — Data Types — PostgreSQL Global Development Group

Easy traps

`WHERE published_at = NULL` never becomes true. Use `IS NULL` for NULL checks.
Validate sample code on a local or test database with recoverable backups before applying it to production data.

Exercise

Choose types for lesson duration, rating, an optional video URL, and publication time, then justify each choice.

You'll know it worked when: You have an `app.tutorials` table whose column types match their domain meaning.