Getting Started with PostgreSQLLesson 1.4

PostgreSQL data types explained with examples

integer types, text vs varchar, boolean, date and timestamp, numeric precision, serial vs identity, UUID

PostgreSQL Data Types

Choosing the right type matters for storage, performance, and correctness. Here are the types you will use in 95% of tables.

Numeric

SMALLINT      -- 2 bytes, -32768 to 32767
INTEGER       -- 4 bytes, ~2.1 billion
BIGINT        -- 8 bytes, very large numbers
NUMERIC(10,2) -- exact, use for money
REAL          -- 4-byte float, imprecise

Text

TEXT          -- unlimited length, preferred
VARCHAR(n)    -- up to n characters
CHAR(n)       -- fixed length, padded

Use TEXT by default. VARCHAR(n) only when you need the database to enforce a length limit. There is no performance difference between the two in PostgreSQL.

Date and time

DATE          -- 2024-06-15
TIME          -- 14:30:00
TIMESTAMP     -- 2024-06-15 14:30:00
TIMESTAMPTZ  -- timestamp with time zone

Other essentials

BOOLEAN       -- true / false
SERIAL        -- auto-increment integer (legacy)
GENERATED ALWAYS AS IDENTITY  -- modern auto-increment
UUID          -- universally unique ID

Prefer GENERATED ALWAYS AS IDENTITY over SERIAL for new tables — it is the SQL standard and behaves more predictably.