Creating Tables & Data Types

A table is defined by its columns, and each column has a data type. The most useful: text for strings, integer/bigint for whole numbers, numeric(12,2) for money (exact — never use float for money), boolean, date, and timestamptz for moments in time.

Every table should have a primary key — a column that uniquely identifies each row. bigint GENERATED ALWAYS AS IDENTITY makes PostgreSQL number rows automatically.

NOT NULL makes a column required; DEFAULT fills in a value when none is given. Change a table later with ALTER TABLE, and remove it with DROP TABLE.

tables.sqlSQL

CREATE TABLE students (
    id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    full_name   text        NOT NULL,
    form        smallint    NOT NULL,
    gender      char(1),
    birth_date  date,
    email       text,
    fee_balance numeric(12,2) NOT NULL DEFAULT 0,
    created_at  timestamptz NOT NULL DEFAULT now()
);

ALTER TABLE students ADD COLUMN phone text;
ALTER TABLE students ALTER COLUMN gender SET NOT NULL;

\d students

Runs in your browser · PostgreSQL

Key points

Pick precise types: numeric for money, timestamptz for times, text for strings.
Every table needs a primary key; identity columns number rows for you.
Use NOT NULL and DEFAULT to keep data complete.

Exercise

Create a teachers table with id, full_name (required), subject, phone, hired_on (date) and salary (numeric). Add an is_active boolean NOT NULL DEFAULT true column with ALTER TABLE, then inspect it with \d teachers.

Show solution

Try the exercise yourself first — then compare your approach with this one.

Salary is money, so it uses numeric(12,2) (exact) rather than a floating-point type. The new column has a default, so existing rows are filled in automatically.

teachers.sqlSQL

CREATE TABLE teachers (
    id        bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    full_name text NOT NULL,
    subject   text,
    phone     text,
    hired_on  date,
    salary    numeric(12,2)
);

ALTER TABLE teachers ADD COLUMN is_active boolean NOT NULL DEFAULT true;

\d teachers

Runs in your browser · PostgreSQL

Key points

Exercise

Check your understanding