Learn SQL with PostgreSQL - Data Types & Constraints Management

#Introduction

Welcome to episode 3 of the Learn SQL with PostgreSQL series! In episode 2, we learned how to design tables with DDL. Now we'll dive deeper into the raw materials of every table: data types and constraints. Understanding both determines how safe, fast, and correct your database will be later on.

In this episode, we'll go through PostgreSQL's main data types one by one — numeric, character, date and time, boolean, and UUID — then learn how to manage constraints correctly.

#Numeric Data Types

PostgreSQL provides a fairly complete family of numeric types, and each type has a different use case. Choosing the right type is an important design decision.

#Integer, BIGINT, and Precise Decimal

SELECT 0.1::REAL + 0.2::REAL AS floating_error;
SELECT 0.1::NUMERIC(10,2) + 0.2::NUMERIC(10,2) AS exact_sum;

#SERIAL vs GENERATED ALWAYS AS IDENTITY

For auto-increment columns, the older generation uses SERIAL; modern SQL standards recommend GENERATED ALWAYS AS IDENTITY, which is more explicit and can't be overridden.

CREATE TABLE old_style (
    id SERIAL PRIMARY KEY
);
 
CREATE TABLE modern_style (
    id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY
);

With GENERATED ALWAYS, PostgreSQL rejects manual id values — the database manages the values itself, avoiding duplicate-id bugs.

#Character Data Types

PostgreSQL provides three types for text: VARCHAR(n) (text with a length limit), CHAR(n) (fixed-length text, short values are padded with spaces), and TEXT (text without a length limit). One important fact: there is no performance penalty between VARCHAR and TEXT — the length limit in VARCHAR(n) is purely validation, not a storage optimization.

CREATE TABLE articles (
    title VARCHAR(200) NOT NULL,
    slug TEXT NOT NULL,
    country CHAR(2) NOT NULL
);

#Date and Time Data Types

#TIMESTAMP vs TIMESTAMPTZ

• TIMESTAMP: date and time WITHOUT timezone information. A value like 2026-08-03 10:00:00 is interpreted "as-is".
• TIMESTAMPTZ: date and time WITH timezone awareness. The value is stored in UTC internally, and rendered according to the session timezone.

SELECT now() AS waktu_sekarang;
SELECT now() AT TIME ZONE 'Asia/Jakarta' AS waktu_jakarta;
SHOW timezone;

#DATE, TIME, and INTERVAL

SELECT now() + INTERVAL '7 days' AS seminggu_lagi;
SELECT age('2026-08-03', '2020-01-01') AS umur_akun;

#Boolean and UUID

#Boolean

CREATE TABLE users (
    id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    email TEXT NOT NULL UNIQUE,
    is_active BOOLEAN NOT NULL DEFAULT TRUE
);

#UUID

UUID is a 128-bit identity that is practically impossible to collide across systems.

CREATE EXTENSION IF NOT EXISTS pgcrypto;
CREATE TABLE sessions (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id INTEGER NOT NULL,
    token TEXT NOT NULL
);

The gen_random_uuid() function generates random UUID v4 values; in PostgreSQL 13+ it's built in without an extension.

#Constraints Management

Constraints are the database's "police" that ensure incoming data always meets the rules — the last line of defense that works even if the application forgets to validate.

#NOT NULL, UNIQUE, and PRIMARY KEY

CREATE TABLE customers (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    email TEXT NOT NULL UNIQUE,
    phone TEXT,
    referral_code TEXT UNIQUE
);

• NOT NULL: the column is required; phone above can be empty because it has no constraint.
• UNIQUE: no duplicate values allowed; one email for only one customer.
• PRIMARY KEY: a combination of NOT NULL + UNIQUE + row identity.

#FOREIGN KEY with Referential Actions

CREATE TABLE orders (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    customer_id UUID NOT NULL REFERENCES customers(id) ON DELETE CASCADE,
    total NUMERIC(12,2) NOT NULL CHECK (total >= 0)
);
 
CREATE TABLE order_items (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    order_id UUID NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
    product_name TEXT NOT NULL
);

#CHECK Constraint

CHECK is a free-form custom value validation — the last line of defense that works even if the application forgets to validate.

CREATE TABLE products (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name TEXT NOT NULL,
    price NUMERIC(12,2) NOT NULL CHECK (price > 0),
    stock INTEGER NOT NULL DEFAULT 0 CHECK (stock >= 0)
);

#Closing

Key takeaways:

• Use NUMERIC for money, not REAL — floating point can ruin precision.
• Prefer GENERATED ALWAYS AS IDENTITY over SERIAL for modern auto-increment.
• Always use TIMESTAMPTZ to store points in time, unless there's a special reason.
• UUID is suitable for global identity and distributed applications.
• Constraints are the database's last line of defense — CHECK, FK with referential actions, and UNIQUE are your best friends.

In episode 4, we'll start filling the database with data: Data Manipulation Language (DML) and Basic Querying — from INSERT to insert data, UPDATE to modify it, DELETE to remove it, up to PostgreSQL's signature RETURNING feature which is very useful in the real world. Get the tables you created ready, because the next episode is full of hands-on practice!