PostgreSQL Data Types

PostgreSQL has one of the richest type systems of any database. Choosing the right type for each column matters — it affects storage, performance, indexing, and data integrity. This chapter covers the most important types and when to use each one.

Numeric Types

CREATE TABLE products (
    id           BIGINT PRIMARY KEY,
    quantity     INTEGER,
    price        NUMERIC(10, 2),   -- up to 10 digits, 2 after decimal point
    weight_kg    REAL,
    rating       SMALLINT CHECK (rating BETWEEN 1 AND 5)
);

-- NUMERIC is exact; FLOAT is approximate
SELECT 0.1 + 0.2;                   -- returns 0.30000000000000004 (float)
SELECT 0.1::NUMERIC + 0.2::NUMERIC; -- returns 0.3 (exact)

Character Types

-- All three store the same values, but TEXT is preferred in PostgreSQL
CREATE TABLE demo (
    code     CHAR(3),       -- always 3 chars, padded: 'US ' not 'US'
    name     VARCHAR(100),  -- up to 100 chars
    bio      TEXT           -- unlimited length
);

Date and Time Types

This is where PostgreSQL truly shines — and where beginners make the most mistakes.

The Critical TIMESTAMP vs TIMESTAMPTZ Difference

TIMESTAMP stores the raw date/time values with no timezone context. TIMESTAMPTZ (timestamp with time zone) stores the moment in UTC and displays it in the session's timezone.

-- Set different timezone for demonstration
SET timezone = 'America/New_York';

INSERT INTO events (name, ts_notz, ts_withtz) VALUES (
    'conference',
    '2024-03-15 10:00:00',           -- stored exactly as-is
    '2024-03-15 10:00:00'            -- stored as UTC (15:00:00 UTC)
);

SET timezone = 'Asia/Tokyo';

SELECT name, ts_notz, ts_withtz FROM events;
-- ts_notz:   2024-03-15 10:00:00      (unchanged — no conversion)
-- ts_withtz: 2024-03-16 00:00:00+09   (converted to Tokyo time correctly!)

Working with Dates and Times

-- Current timestamp (timezone-aware)
SELECT NOW();               -- 2024-03-15 14:23:11.456789+00
SELECT CURRENT_TIMESTAMP;   -- same
SELECT CURRENT_DATE;        -- 2024-03-15
SELECT CURRENT_TIME;        -- 14:23:11.456789+00

-- Interval arithmetic
SELECT NOW() + INTERVAL '7 days';           -- one week from now
SELECT NOW() - INTERVAL '3 months';         -- 3 months ago
SELECT '2024-12-31'::DATE - '2024-01-01'::DATE;  -- 365 (days)

-- Extract parts
SELECT EXTRACT(YEAR  FROM NOW());   -- 2024
SELECT EXTRACT(MONTH FROM NOW());   -- 3
SELECT EXTRACT(DOW   FROM NOW());   -- 0=Sunday, 1=Monday, ...
SELECT DATE_TRUNC('month', NOW());  -- 2024-03-01 00:00:00+00

-- Format a date
SELECT TO_CHAR(NOW(), 'YYYY-MM-DD HH24:MI:SS');  -- 2024-03-15 14:23:11

-- Parse a string into a date
SELECT TO_DATE('15/03/2024', 'DD/MM/YYYY');  -- 2024-03-15

Boolean

-- Boolean literals
SELECT TRUE, FALSE, NULL;

-- Valid input values (PostgreSQL is lenient)
-- TRUE:  true, yes, on, 1, t, y
-- FALSE: false, no, off, 0, f, n

CREATE TABLE feature_flags (
    feature_name TEXT,
    enabled      BOOLEAN DEFAULT FALSE
);

INSERT INTO feature_flags VALUES ('dark_mode', TRUE);
INSERT INTO feature_flags VALUES ('beta_ui',   'yes');   -- also valid
INSERT INTO feature_flags VALUES ('analytics', '1');     -- also valid

SELECT feature_name FROM feature_flags WHERE enabled;  -- WHERE enabled = TRUE

Network Address Types

PostgreSQL has built-in types for network addresses — far better than storing them as strings.

CREATE TABLE servers (
    hostname   TEXT,
    ip_address INET,    -- IPv4 or IPv6 address, optionally with subnet
    network    CIDR,    -- Network address (host bits must be zero)
    mac_addr   MACADDR  -- MAC address
);

INSERT INTO servers VALUES
    ('web01',   '192.168.1.10',      '192.168.1.0/24',   '08:00:2b:01:02:03'),
    ('db01',    '10.0.0.5/16',       '10.0.0.0/8',       '08:00:2b:04:05:06');

-- Find all IPs in a subnet
SELECT hostname FROM servers
WHERE ip_address << '192.168.0.0/16';  -- << means "contained within"

-- Find the network address of an IP
SELECT host(ip_address), masklen(ip_address) FROM servers;

UUID

-- Enable the uuid-ossp extension
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

CREATE TABLE users (
    id         UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    username   TEXT NOT NULL
);

INSERT INTO users (username) VALUES ('alice');
-- id is automatically generated: 'a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11'

-- PostgreSQL 13+ has gen_random_uuid() built in (no extension needed)
CREATE TABLE sessions (
    id         UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id    UUID REFERENCES users(id)
);

Enumerated Types

-- Create an enum type
CREATE TYPE order_status AS ENUM ('pending', 'processing', 'shipped', 'delivered', 'cancelled');

CREATE TABLE orders (
    id       SERIAL PRIMARY KEY,
    status   order_status NOT NULL DEFAULT 'pending'
);

INSERT INTO orders (status) VALUES ('processing');
INSERT INTO orders (status) VALUES ('invalid');  -- ERROR: invalid input value for enum

-- Enums have a sort order
SELECT unnest(enum_range(NULL::order_status));
-- pending, processing, shipped, delivered, cancelled

SELECT * FROM orders WHERE status > 'pending';  -- later in enum sequence

JSON and JSONB (Preview)

JSON and JSONB are covered in detail in chapter 17, but here is the key difference:

CREATE TABLE settings (
    user_id INTEGER,
    prefs   JSONB  -- always use JSONB unless you need exact whitespace preservation
);

Arrays (Preview)

-- Any type can be an array
CREATE TABLE tags_demo (
    post_id INTEGER,
    tags    TEXT[],
    scores  INTEGER[]
);

INSERT INTO tags_demo VALUES (1, ARRAY['postgresql', 'database'], ARRAY[95, 87]);
INSERT INTO tags_demo VALUES (2, '{sql, beginner}', '{70, 82}');

Type Casting

PostgreSQL uses :: for casting:

SELECT '42'::INTEGER;             -- text to integer
SELECT 42::TEXT;                  -- integer to text
SELECT '2024-03-15'::DATE;        -- text to date
SELECT '3.14'::NUMERIC;           -- text to numeric
SELECT NOW()::DATE;               -- timestamp to date (drops time)

-- CAST() is the SQL-standard equivalent
SELECT CAST('42' AS INTEGER);

Type Comparison Quick Reference