SQL Data Types

Why Data Types Matter

Choosing the right data type is one of the most impactful decisions in schema design. The right type enforces data integrity (PostgreSQL rejects values that don’t fit), enables efficient storage, and unlocks type-specific operators and functions. A price stored as TEXT can’t be summed; a date stored as TEXT can’t be compared with < reliably. Getting types right at the start prevents an entire class of bugs and performance problems downstream.

Numeric Types

PostgreSQL offers several numeric types depending on your precision and range needs. The distinction between exact and approximate types is especially important for financial data — floating-point types like REAL and DOUBLE PRECISION cannot represent all decimal values exactly, which causes rounding errors that compound over calculations.

CREATE TABLE numeric_examples (
    small_count   SMALLINT,          -- -32,768 to 32,767 (2 bytes)
    regular_count INTEGER,           -- -2.1B to 2.1B (4 bytes), alias: INT
    big_count     BIGINT,            -- ~9.2 quintillion (8 bytes)
    price         NUMERIC(10, 2),    -- exact decimal: up to 10 digits, 2 after point
    tax_rate      DECIMAL(5, 4),     -- DECIMAL is an alias for NUMERIC
    measurement   REAL,              -- 6 decimal digits of precision (4 bytes, floating point)
    precise_val   DOUBLE PRECISION,  -- 15 decimal digits of precision (8 bytes)
    auto_id       SERIAL,            -- auto-incrementing INTEGER (1, 2, 3, ...)
    big_auto_id   BIGSERIAL          -- auto-incrementing BIGINT
);

Key guidance:

• Use INTEGER for most counts, IDs, and quantities.
• Use BIGINT or BIGSERIAL for IDs in high-volume tables (anything that could exceed ~2 billion rows).
• Use NUMERIC (exact decimal) for money and any calculation where floating-point rounding is unacceptable. Never use REAL or DOUBLE PRECISION for financial values.
• SERIAL and BIGSERIAL are shorthand for creating a sequence and setting it as the column default. The modern alternative is GENERATED ALWAYS AS IDENTITY.

Character Types

PostgreSQL’s text storage is simpler than most databases: TEXT and VARCHAR are stored identically and perform identically. The only reason to use VARCHAR(n) is when you want the database to enforce a maximum length as a constraint — for everything else, TEXT is cleaner and avoids the arbitrary length guessing that VARCHAR encourages.

CREATE TABLE text_examples (
    code        CHAR(3),         -- fixed-length, always padded to 3 chars
    username    VARCHAR(50),     -- variable-length, max 50 chars enforced
    description TEXT             -- unlimited length, same performance as VARCHAR
);

CHAR(n) is almost never the right choice in PostgreSQL. It pads shorter strings with spaces, which causes subtle bugs in comparisons and wastes storage.

Date and Time Types

Time-related bugs are among the most frustrating to debug — they often only surface when users are in different timezones or when a server migrates regions. PostgreSQL’s TIMESTAMPTZ type eliminates most of these problems by storing everything as UTC and converting at display time. Make it your default for any timestamp column.

CREATE TABLE event_log (
    event_date   DATE,           -- just a calendar date: 2024-03-15
    start_time   TIME,           -- time of day without date: 14:30:00
    created_at   TIMESTAMP,      -- date + time, no timezone: 2024-03-15 14:30:00
    updated_at   TIMESTAMPTZ,    -- date + time, stored as UTC: recommended default
    duration     INTERVAL        -- a span of time: '2 hours 30 minutes'
);

-- TIMESTAMPTZ automatically converts to/from the session timezone
SET timezone = 'America/New_York';
INSERT INTO event_log (updated_at) VALUES ('2024-03-15 14:30:00+05:30');
SELECT updated_at FROM event_log;
-- Returns: 2024-03-15 05:00:00-04  (converted to New York time)

Use INTERVAL for durations and arithmetic — it keeps time math readable and correct:

-- Add exactly 7 days to the current timestamp
SELECT NOW() + INTERVAL '7 days' AS one_week_from_now;

-- Calculate someone's age between two dates
SELECT AGE('2024-12-31', '1990-05-15') AS age;

Boolean

Boolean columns are simple but often underused — developers sometimes store 'Y'/'N' or 1/0 as text or integers instead. PostgreSQL’s native BOOLEAN type is cleaner, more expressive, and enables direct use in WHERE clauses without comparison operators.

CREATE TABLE feature_flags (
    feature_name TEXT PRIMARY KEY,
    is_enabled   BOOLEAN NOT NULL DEFAULT FALSE
);

INSERT INTO feature_flags VALUES ('dark_mode', TRUE), ('beta_ui', FALSE);

-- Boolean columns can be used directly in WHERE without = TRUE
SELECT * FROM feature_flags WHERE is_enabled;
SELECT * FROM feature_flags WHERE NOT is_enabled;

PostgreSQL accepts TRUE/FALSE, 't'/'f', 'yes'/'no', 'on'/'off', and 1/0 as boolean literals. Stick to TRUE and FALSE for clarity.

UUID

UUIDs are 128-bit identifiers useful as primary keys when you need globally unique IDs — for example, in distributed systems where multiple nodes insert rows independently, or when you don’t want sequential IDs exposed in URLs (a user shouldn’t be able to guess that user/1001 exists just because they know user/1000 does).

CREATE EXTENSION IF NOT EXISTS pgcrypto;

CREATE TABLE sessions (
    id         UUID PRIMARY KEY DEFAULT gen_random_uuid(),  -- auto-generate on insert
    user_id    INTEGER,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

PostgreSQL 13+ has gen_random_uuid() built in, no extension needed.

JSON and JSONB

PostgreSQL’s JSON support lets you store flexible, schema-less documents alongside structured relational data. This is useful for attributes that vary by record type — product specifications, user preferences, event metadata. Use a JSONB column rather than moving to a separate document database whenever the variable data is secondary to the main relational structure.

CREATE TABLE user_profiles (
    id       SERIAL PRIMARY KEY,
    username TEXT NOT NULL,
    settings JSONB  -- use JSONB, not JSON — it supports indexing and is faster to query
);

INSERT INTO user_profiles (username, settings) VALUES
    ('alice', '{"theme": "dark", "notifications": {"email": true, "sms": false}}');

-- -> returns a JSON value; ->> returns plain text
SELECT username, settings->>'theme' AS theme FROM user_profiles;
SELECT username FROM user_profiles WHERE settings->'notifications'->>'email' = 'true';

Always use JSONB over JSON. JSONB stores data in a binary format that supports indexing and is faster to query. JSON stores the raw text and re-parses it on every access.

Arrays

PostgreSQL supports arrays of any type, which is useful for multi-valued attributes that don’t warrant a separate table — tags, permissions, a list of notification channels. Arrays support containment operators and can be indexed with GIN indexes for fast lookups.

CREATE TABLE posts (
    id   SERIAL PRIMARY KEY,
    title TEXT,
    tags TEXT[]  -- array of text values
);

INSERT INTO posts (title, tags) VALUES
    ('Getting Started with SQL', ARRAY['sql', 'databases', 'beginner']);

-- ANY checks if a value exists anywhere in the array
SELECT title FROM posts WHERE 'sql' = ANY(tags);

-- Append to an array with the || operator
UPDATE posts SET tags = tags || ARRAY['tutorial'] WHERE id = 1;

Type Casting

PostgreSQL is strict about types — you can’t add a number to a string without an explicit cast. The :: operator is the idiomatic PostgreSQL shorthand; CAST() is the portable SQL standard form. Casting is commonly needed when working with imported text data or combining values of different types in expressions.

SELECT '42'::INTEGER + 8;               -- 50
SELECT 3.14::TEXT;                       -- '3.14'
SELECT '2024-01-15'::DATE;              -- 2024-01-15
SELECT '1 hour 30 minutes'::INTERVAL;   -- 01:30:00

-- Standard SQL syntax (portable across databases)
SELECT CAST('42' AS INTEGER) + 8;

Choosing Types in Practice

A typical application table should use BIGSERIAL for a safe auto-incrementing ID, NUMERIC for any money column, TEXT for string data, JSONB for flexible extra attributes, and TIMESTAMPTZ for all timestamps. This pattern covers the vast majority of real-world needs.

CREATE TABLE orders (
    id           BIGSERIAL PRIMARY KEY,          -- auto-increment, safe for large tables
    user_id      BIGINT NOT NULL,
    total_amount NUMERIC(12, 2) NOT NULL,        -- exact decimal for money
    status       TEXT NOT NULL DEFAULT 'pending',
    metadata     JSONB,                          -- flexible extra data
    created_at   TIMESTAMPTZ NOT NULL DEFAULT NOW(), -- UTC timestamp
    shipped_at   TIMESTAMPTZ                     -- nullable: NULL until shipped
);