PostgreSQL Zero to Advanced #2: Tables, Data Types & Constraints

In Part 1 we installed PostgreSQL and created the course_platform database. Now it is time to design the real tables for our online course platform. A good table design is the foundation of every fast, bug-free application, and PostgreSQL gives you powerful tools to enforce the rules of your data inside the database, not just in application code.

Make sure you are connected to the right database before continuing:

psql -h localhost -U postgres -d course_platform

Choosing data types

Every column has a data type. Picking the right one saves storage, prevents invalid values and makes queries faster. These are the types you will use most often:

Type	Use it for	Example
INTEGER / BIGINT	Whole numbers, IDs, counters	42
NUMERIC(p, s)	Exact decimals such as money with cents	19.99
VARCHAR(n) / TEXT	Strings with or without a length limit	'Budi'
BOOLEAN	True/false flags	true
DATE	Calendar date without time	'2025-01-10'
TIMESTAMPTZ	Point in time, timezone-aware	'2025-07-01 09:15:00+00'
UUID	Globally unique identifiers	gen_random_uuid()
JSONB	Semi-structured data (Part 10)	'{"lang": "id"}'

Two rules of thumb: use TIMESTAMPTZ instead of TIMESTAMP for real events, and never store money in REAL or DOUBLE PRECISION because floating point is not exact. Our platform sells courses in Rupiah, which has no cents, so we store prices as INTEGER. For currencies with decimals, use NUMERIC(12, 2).

Creating the tables

Our platform needs three tables: users (students), courses (what we sell) and orders (who bought what). Let's start with users:

CREATE TABLE users (
    id        INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name      VARCHAR(100) NOT NULL,
    email     VARCHAR(255) NOT NULL UNIQUE,
    city      VARCHAR(50),
    joined_at DATE NOT NULL DEFAULT CURRENT_DATE
);

Output:

CREATE TABLE

Line by line:

• GENERATED ALWAYS AS IDENTITY makes PostgreSQL generate the ID automatically (1, 2, 3, ...). It is the modern, SQL-standard replacement for SERIAL.
• PRIMARY KEY means the value is unique and never NULL. Every table should have one.
• NOT NULL forces a value. A user without a name or email makes no sense.
• UNIQUE on email prevents two accounts with the same email.
• city has no NOT NULL, so it is optional.
• DEFAULT CURRENT_DATE fills in today's date when no value is given.

Next, the courses table with a couple of CHECK constraints:

CREATE TABLE courses (
    id              INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    title           VARCHAR(200) NOT NULL,
    category        VARCHAR(50)  NOT NULL,
    price           INTEGER      NOT NULL CHECK (price >= 0),
    level           VARCHAR(20)  NOT NULL DEFAULT 'beginner'
                    CHECK (level IN ('beginner', 'intermediate', 'advanced')),
    is_published    BOOLEAN      NOT NULL DEFAULT true,
    prerequisite_id INTEGER
);

Output:

CREATE TABLE

prerequisite_id will store the ID of another course that should be taken first. We will turn it into a real relationship in Part 6. Finally, the orders table:

CREATE TABLE orders (
    id         INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    user_id    INTEGER     NOT NULL,
    course_id  INTEGER     NOT NULL,
    amount     INTEGER     NOT NULL CHECK (amount >= 0),
    status     VARCHAR(20) NOT NULL DEFAULT 'pending'
               CHECK (status IN ('pending', 'paid', 'refunded')),
    ordered_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

Output:

CREATE TABLE

Let's confirm the three tables exist:

\dt

Output:

          List of relations
 Schema |  Name   | Type  |  Owner   
--------+---------+-------+----------
 public | courses | table | postgres
 public | orders  | table | postgres
 public | users   | table | postgres
(3 rows)

Constraints in action

Constraints are rules that PostgreSQL checks on every INSERT and UPDATE. If a row breaks a rule, the whole statement is rejected. Let's try a valid insert first. We only pass name and email; id and joined_at are filled automatically:

INSERT INTO users (name, email)
VALUES ('Budi Santoso', 'budi@example.com');

Output:

INSERT 0 1

NOT NULL

What happens if we forget the name?

INSERT INTO users (email)
VALUES ('noname@example.com');

Output:

ERROR:  null value in column "name" of relation "users" violates not-null constraint
DETAIL:  Failing row contains (2, null, noname@example.com, null, 2026-10-13).

The error tells you exactly which column and which row failed. Notice joined_at in the failing row was already filled with today's date by the DEFAULT.

UNIQUE

INSERT INTO users (name, email)
VALUES ('Budi Lain', 'budi@example.com');

Output:

ERROR:  duplicate key value violates unique constraint "users_email_key"
DETAIL:  Key (email)=(budi@example.com) already exists.

PostgreSQL automatically named the constraint users_email_key and created an index behind it.

CHECK

A negative price and an unknown level are both rejected:

INSERT INTO courses (title, category, price)
VALUES ('Free Money Course', 'misc', -5000);

Output:

ERROR:  new row for relation "courses" violates check constraint "courses_price_check"
DETAIL:  Failing row contains (1, Free Money Course, misc, -5000, beginner, t, null).

INSERT INTO courses (title, category, price, level)
VALUES ('SQL Ninja', 'database', 100000, 'expert');

Output:

ERROR:  new row for relation "courses" violates check constraint "courses_level_check"
DETAIL:  Failing row contains (2, SQL Ninja, database, 100000, expert, t, null).

DEFAULT

If we skip level and is_published, the defaults kick in:

INSERT INTO courses (title, category, price)
VALUES ('Test Course', 'database', 100000);

SELECT id, title, level, is_published FROM courses;

Output:

INSERT 0 1
 id |    title    |  level   | is_published 
----+-------------+----------+--------------
  3 | Test Course | beginner | t
(1 row)

Interesting: the new course got id = 3, not 1. The two failed inserts above had already taken values 1 and 2 from the identity sequence. Sequences never roll back, so gaps in IDs are normal. Never rely on IDs being consecutive.

Changing a table with ALTER TABLE

Requirements change. You can add a constraint to an existing table at any time. Here we add a (very simple) email format check:

ALTER TABLE users
ADD CONSTRAINT users_email_format CHECK (email LIKE '%@%');

Output:

ALTER TABLE

Use \d to see the full structure of a table, including its indexes and constraints:

\d users

Output:

                                   Table "public.users"
  Column   |          Type          | Collation | Nullable |           Default            
-----------+------------------------+-----------+----------+------------------------------
 id        | integer                |           | not null | generated always as identity
 name      | character varying(100) |           | not null | 
 email     | character varying(255) |           | not null | 
 city      | character varying(50)  |           |          | 
 joined_at | date                   |           | not null | CURRENT_DATE
Indexes:
    "users_pkey" PRIMARY KEY, btree (id)
    "users_email_key" UNIQUE CONSTRAINT, btree (email)
Check constraints:
    "users_email_format" CHECK (email::text ~~ '%@%'::text)

Other useful variants are ALTER TABLE ... ADD COLUMN, DROP COLUMN, RENAME COLUMN and ALTER COLUMN ... SET NOT NULL. We will use several of them later in the series.

Cleaning up the test data

We inserted a couple of test rows. Let's wipe them and reset the identity counters so Part 3 starts with clean IDs:

TRUNCATE users, courses RESTART IDENTITY;

Output:

TRUNCATE TABLE

TRUNCATE is much faster than DELETE for emptying whole tables, and RESTART IDENTITY resets the ID sequences back to 1. Be careful: there is no WHERE clause, so it always removes everything.

Common mistakes

• Using FLOAT for money. 0.1 + 0.2 is not exactly 0.3 in floating point. Use INTEGER (smallest unit) or NUMERIC.
• Making everything nullable. If a value is required by the business, declare NOT NULL. Validating only in the application lets bad data sneak in through scripts and other services.
• Using TIMESTAMP without time zone for events. It silently drops timezone information. Prefer TIMESTAMPTZ.
• Mixed-case identifiers. CREATE TABLE "Users" forces you to quote the name forever. Stick to snake_case.
• Expecting IDs without gaps. Failed inserts and rollbacks consume sequence values. That is by design.

Exercise

1. Create a table coupons with columns id (identity primary key), code (unique, not null, max 20 characters), discount_percent (integer between 1 and 100) and expires_at (TIMESTAMPTZ, not null).
2. Try inserting a coupon with discount_percent = 150 and read the error message.
3. Add a column is_active BOOLEAN NOT NULL DEFAULT true with ALTER TABLE and check the result with \d coupons.
4. Drop the table when you are done.

Summary

You created the three core tables of the course platform, picked appropriate data types, and saw PRIMARY KEY, NOT NULL, UNIQUE, CHECK and DEFAULT reject bad data with clear error messages. You also learned that identity gaps are normal and how to inspect a table with \d.

In Part 3 we will fill these tables with real sample data and master CRUD: INSERT, SELECT, UPDATE, DELETE and the very handy RETURNING clause.