Tables and Data Types
PostgreSQL's rich type system: integer, text, numeric, boolean, date, UUID, JSON
Skills taught: pg.create-table, pg.data-types, pg.serial, pg.uuid, pg.timestamps
CREATE TABLE with real-world types
CREATE TABLE users (
id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
email TEXT NOT NULL UNIQUE,
username TEXT NOT NULL UNIQUE,
full_name TEXT,
is_active BOOLEAN NOT NULL DEFAULT TRUE,
role TEXT NOT NULL DEFAULT 'member',
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE TABLE posts (
id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
user_id BIGINT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
title TEXT NOT NULL,
body TEXT,
published BOOLEAN NOT NULL DEFAULT FALSE,
views INTEGER NOT NULL DEFAULT 0,
published_at TIMESTAMPTZ,
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
Key type decisions
-- Primary keys: BIGINT IDENTITY (not SERIAL — deprecated in modern Postgres)
-- SERIAL is still supported but IDENTITY is the SQL standard
id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY
-- Or UUID primary keys (better for distributed systems, no guessable IDs)
id UUID PRIMARY KEY DEFAULT gen_random_uuid()
-- Money/prices: NUMERIC, never FLOAT
-- FLOAT has rounding errors: 0.1 + 0.2 ≠ 0.3
price NUMERIC(10, 2) -- up to 99999999.99, exactly 2 decimal places
-- Timestamps: always TIMESTAMPTZ (with timezone)
-- Without TZ: stored as local time — breaks across DST changes and server moves
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
-- Status fields: TEXT with CHECK or a custom enum
status TEXT NOT NULL DEFAULT 'pending' CHECK (status IN ('pending', 'active', 'closed'))
-- Or as an enum:
CREATE TYPE order_status AS ENUM ('pending', 'processing', 'shipped', 'delivered', 'cancelled');
status order_status NOT NULL DEFAULT 'pending'
Tip: Always use TIMESTAMPTZ, never TIMESTAMP. TIMESTAMP stores a 'wall clock' time with no timezone info — it is ambiguous when DST changes or servers move between regions. TIMESTAMPTZ stores UTC internally and converts to local time on output.
PostgreSQL type reference
Integers:
smallint 2 bytes -32,768 to 32,767
integer 4 bytes -2.1B to 2.1B ← use for counts, ages, IDs
bigint 8 bytes ±9.2 quadrillion ← use for IDs in large tables
Exact numeric:
numeric(p,s) variable exact decimal ← use for money
decimal alias for numeric
Floating point (imprecise — not for money):
real 4 bytes 6 decimal digits
double 8 bytes 15 decimal digits
Text:
text unlimited no padding ← use this
varchar(n) ≤n chars with length check
char(n) n chars space-padded (avoid)
Boolean: boolean true/false/null
Date/Time:
date 4 bytes YYYY-MM-DD
time 8 bytes HH:MM:SS.ssssss
timestamp 8 bytes date+time, NO timezone (avoid)
timestamptz 8 bytes date+time, WITH timezone ← use this
interval 16 bytes '3 days', '2 hours 30 minutes'
Identifiers:
uuid 16 bytes 550e8400-e29b-41d4-a716-446655440000
JSON:
json text stored as-is, validated
jsonb binary indexed, queryable ← use this
Frequently Asked Questions
Should I use SERIAL or IDENTITY for auto-increment?
Use GENERATED ALWAYS AS IDENTITY (or GENERATED BY DEFAULT AS IDENTITY). SERIAL is a shorthand that creates a hidden sequence and sets a default — it works but is not SQL-standard. IDENTITY columns are the SQL standard (PostgreSQL 10+) and behave more predictably, especially when copying data between databases. For existing SERIAL columns, no need to migrate — both work fine.
When should I use UUID vs BIGINT for primary keys?
BIGINT is simpler and more efficient for most applications. UUID is better when you: need IDs to be unguessable (security), need to generate IDs client-side before database insert, or are merging data from multiple databases/shards. The downside of UUID is larger index size and slightly slower B-tree operations.