PostgreSQL Foundations·A database you can operate

Types, NULL and schema design

Choose types that match the domain and decide deliberately whether NULL is allowed.

Intermediate 14 min read Lesson 7 of 300 Challenge included

What you will be able to do

Use timestamptz, not timestamp, for real-world events.
Use numeric for money; floating point is for measurements.
Normalize repeated data until you have a measured reason to denormalize.

In plain English

PostgreSQL has rich types: timestamptz, numeric, jsonb, arrays and enums. Prefer timestamptz for moments in time and numeric for exact money. NULL means unknown, not zero or empty string.

types.sqlSQL

create table certificates (
  id text primary key,
  holder_name text not null,
  issued_at timestamptz not null default now(),
  score numeric(5,2) not null check (score between 0 and 100),
  expires_at timestamptz,
  metadata jsonb not null default '{}'::jsonb,
  constraint expiry_after_issue
    check (expires_at is null or expires_at > issued_at)
);

Your turn

Add an expiry constraint

Medium

Create a certificates table with issued_at and expires_at and a constraint that expiry is after issue.

SQL · starter

create table certificates ();

Check your understanding before moving on.

Knowledge checkQuestion 1

Which type is best for an exact monetary amount?

Select an answer to continue

Study tools

Make it yours.

Keep a private note for this lesson, then turn the key ideas into flashcards you can revisit.

Sign in to save notes and flashcards.Both are private to your account and stay with your progress.

Finished reading?

Lock in what you just learned.

Mark this lesson complete to save your place and move one step closer to the certificate.

Next: Joins and aggregates

Lesson 7 of 300 · 0% of course