PostgreSQL Data Types (Very Important in Postgres)

A. Numeric Types

1. Integer Types

sql

-- Integer types with ranges
SMALLINT    -- -32,768 to 32,767 (2 bytes)
INTEGER     -- -2,147,483,648 to 2,147,483,647 (4 bytes) - Most common
BIGINT      -- -9.22e18 to 9.22e18 (8 bytes)
SERIAL      -- Auto-incrementing INTEGER (1 to 2,147,483,647)
BIGSERIAL   -- Auto-incrementing BIGINT (1 to 9.22e18)

-- Examples
CREATE TABLE users (
    id SERIAL PRIMARY KEY,           -- Auto-increment
    age SMALLINT,                    -- Small range
    views INTEGER DEFAULT 0,         -- Regular integer
    social_score BIGINT              -- Large numbers
);

-- SERIAL is actually:
-- INTEGER NOT NULL + DEFAULT nextval('sequence_name')

2. Decimal/Numeric Types (Exact precision)

sql

-- For monetary or exact calculations
NUMERIC(precision, scale)   -- Variable precision, exact
DECIMAL(precision, scale)   -- Same as NUMERIC (alias)

-- precision = total digits
-- scale = digits after decimal point

CREATE TABLE products (
    price NUMERIC(10, 2),      -- 8 digits before, 2 after (like 12345678.99)
    discount NUMERIC(5, 4),     -- 1 before, 4 after (like 0.1234)
    exact_value NUMERIC         -- Unlimited precision! (very large numbers)
);

-- Examples
INSERT INTO products VALUES 
    (123456.78, 0.1234, 123.4567890123456789);
-- NUMERIC is slower but exact - no floating-point errors

3. Floating-Point Types (Approximate)

sql

REAL        -- 6 decimal digits precision (4 bytes)
DOUBLE PRECISION  -- 15 decimal digits precision (8 bytes)

-- Use for scientific calculations where exactness isn't critical
CREATE TABLE measurements (
    temperature REAL,          -- Like 23.4567
    precision DOUBLE PRECISION -- Like 23.4567890123456
);

-- Be careful with equality checks!
SELECT * FROM measurements WHERE temperature = 23.4567; -- Might fail
SELECT * FROM measurements WHERE ABS(temperature - 23.4567) < 0.0001;

B. Character Types

1. Text Types Comparison

sql

VARCHAR(n)   -- Variable length with limit (n = max chars)
CHAR(n)      -- Fixed length, padded with spaces
TEXT         -- Unlimited length (no performance penalty!)

-- PostgreSQL-specific: VARCHAR without (n) = TEXT
VARCHAR      -- Same as TEXT (unlimited)

2. Examples & Best Practices

sql

CREATE TABLE messages (
    -- Fixed format (country codes, status codes)
    country_code CHAR(2),           -- Always 2 chars: 'US', 'FR'
    
    -- Limited variable length
    username VARCHAR(50),           -- Max 50 chars
    email VARCHAR(255),             -- Common email limit
    
    -- Unlimited text
    description TEXT,               -- For large content
    comments TEXT,                  -- No limit needed
    json_data TEXT                  -- Store JSON as text
    
    -- VARCHAR without limit = TEXT
    notes VARCHAR                   -- Same as TEXT
);

-- Best Practice: Use TEXT for most cases
-- TEXT has no performance penalty over VARCHAR
-- Only use VARCHAR(n) when you need constraint enforcement

3. Character Operations

sql

-- Length functions
SELECT LENGTH(description) FROM messages;  -- Character length
SELECT OCTET_LENGTH(description) FROM messages;  -- Bytes

-- Pattern matching
SELECT * FROM messages WHERE username LIKE 'A%';
SELECT * FROM messages WHERE description ~ 'error|warning';  -- Regex

-- String functions
SELECT CONCAT(username, ' (', email, ')') FROM messages;
SELECT SUBSTRING(description FROM 1 FOR 100) FROM messages;
SELECT UPPER(username), LOWER(email) FROM messages;

C. Date & Time Types

1. Types & Characteristics

sql

DATE          -- Only date: '2024-01-15'
TIME          -- Only time: '14:30:00'
TIMESTAMP     -- Date + time WITHOUT timezone: '2024-01-15 14:30:00'
TIMESTAMPTZ   -- Date + time WITH timezone (RECOMMENDED): '2024-01-15 14:30:00+00'
INTERVAL      -- Time span: '1 day 2 hours'

-- Storage sizes
-- DATE: 4 bytes
-- TIMESTAMP: 8 bytes
-- TIMESTAMPTZ: 8 bytes (stores as UTC internally)

2. TIMESTAMP vs TIMESTAMPTZ (CRITICAL!)

sql

-- TIMESTAMP (naive - stores what you give it)
CREATE TABLE events_naive (
    event_time TIMESTAMP
);
INSERT INTO events_naive VALUES ('2024-01-15 14:30:00');

-- TIMESTAMPTZ (timezone-aware - converts to UTC)
CREATE TABLE events_tz (
    event_time TIMESTAMPTZ  -- ALWAYS USE THIS!
);
INSERT INTO events_tz VALUES ('2024-01-15 14:30:00-05');  -- EST

-- Best Practice: ALWAYS use TIMESTAMPTZ
-- It handles timezone conversions automatically

3. Examples & Functions

sql

CREATE TABLE logs (
    id SERIAL PRIMARY KEY,
    created_at TIMESTAMPTZ DEFAULT NOW(),  -- Auto-set to current time
    event_date DATE,
    duration INTERVAL,
    start_time TIME
);

-- Insert with different formats
INSERT INTO logs (event_date, created_at) VALUES
    ('2024-01-15', '2024-01-15 14:30:00+00'),
    ('20240115', '2024-01-15T14:30:00Z'),  -- ISO format
    (CURRENT_DATE, CURRENT_TIMESTAMP);     -- Current time

-- Date/Time functions
SELECT 
    NOW(),                      -- Current timestamp with timezone
    CURRENT_DATE,               -- Today's date
    CURRENT_TIME,               -- Current time
    EXTRACT(YEAR FROM created_at) as year,
    EXTRACT(MONTH FROM created_at) as month,
    DATE_TRUNC('hour', created_at) as hour_start,
    created_at + INTERVAL '1 day' as tomorrow,
    AGE(created_at) as age;     -- Time since timestamp

-- Timezone conversions
SELECT 
    created_at AT TIME ZONE 'UTC' as utc_time,
    created_at AT TIME ZONE 'America/New_York' as ny_time
FROM logs;

D. Boolean Type

sql

-- Simple true/false
BOOLEAN  -- Aliases: BOOL

CREATE TABLE tasks (
    id SERIAL PRIMARY KEY,
    is_complete BOOLEAN DEFAULT FALSE,
    is_active BOOL,
    is_verified BOOLEAN
);

-- Valid boolean values
INSERT INTO tasks (is_complete, is_active) VALUES
    (TRUE, TRUE),      -- true
    (FALSE, FALSE),    -- false
    ('t', 'f'),        -- true/false
    ('yes', 'no'),     -- yes/no
    ('1', '0'),        -- 1/0
    ('on', 'off'),     -- on/off
    (NULL, NULL);      -- NULL

-- Boolean operations
SELECT * FROM tasks WHERE is_complete = TRUE;
SELECT * FROM tasks WHERE NOT is_complete;
SELECT * FROM tasks WHERE is_complete IS NULL;
SELECT * FROM tasks WHERE is_complete IS NOT FALSE;

-- Useful for flags and status indicators

E. UUID Type

sql

-- Universally Unique Identifier (128-bit)
-- Format: 123e4567-e89b-12d3-a456-426614174000
-- Requires extension for generation

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

CREATE TABLE users (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),  -- Version 4 (random)
    -- or DEFAULT uuid_generate_v1()  -- Version 1 (time-based)
    username VARCHAR(50)
);

-- Insert with generated UUID
INSERT INTO users (username) VALUES ('alice');
-- id auto-generated: e.g., 'a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11'

-- Manual insert
INSERT INTO users (id, username) VALUES 
    ('123e4567-e89b-12d3-a456-426614174000', 'bob');

-- Benefits over SERIAL:
-- 1. Globally unique (good for distributed systems)
-- 2. Obfuscates sequence (security)
-- 3. Merge data from different databases easily

-- Storage: 16 bytes (vs 4 bytes for INTEGER)

F. Arrays (PostgreSQL-Specific!)

1. Basic Array Usage

sql

-- Array of any data type: TYPE[]
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name TEXT,
    tags TEXT[],                    -- Array of text
    prices NUMERIC[],               -- Array of numbers
    available_sizes INTEGER[],      -- Array of integers
    schedule TIMESTAMPTZ[]          -- Array of timestamps
);

-- Insert array values
INSERT INTO products (name, tags, prices) VALUES
    ('Laptop', '{"electronics", "sale", "new"}', '{999.99, 899.99}'),
    ('Book', ARRAY['fiction', 'bestseller'], ARRAY[19.99, 14.99]);

-- Access elements (1-indexed!)
SELECT 
    tags[1] as primary_tag,         -- First element
    tags[2:3] as other_tags         -- Slice (elements 2-3)
FROM products;

2. Array Functions & Operators

sql

-- Array functions
SELECT 
    array_length(tags, 1) as tag_count,
    CARDINALITY(tags) as total_tags,      -- Number of elements
    unnest(tags) as individual_tag        -- Expand array to rows
FROM products;

-- Array operators
SELECT * FROM products WHERE
    'electronics' = ANY(tags),            -- Element exists
    'sale' = ALL(ARRAY['sale', 'new']),   -- All elements match
    tags @> '{"sale"}',                   -- Contains
    tags && '{"sale", "clearance"}',      -- Overlaps
    tags <@ '{"electronics", "sale", "new", "used"}';  -- Contained by

-- Modify arrays
UPDATE products SET 
    tags = array_append(tags, 'featured'),
    prices = array_prepend(1099.99, prices);

-- 2D arrays
CREATE TABLE matrices (
    id SERIAL,
    matrix INTEGER[][]
);
INSERT INTO matrices VALUES (1, '{{1,2,3},{4,5,6},{7,8,9}}');

G. JSON / JSONB (PostgreSQL Superpower!)

1. JSON vs JSONB

sql

-- JSON: Stores exact text, preserves whitespace, order
-- JSONB: Binary storage, no whitespace, no order, INDEXABLE!

CREATE TABLE documents (
    id SERIAL,
    metadata JSON,      -- Exact storage
    data JSONB          -- Binary storage (USE THIS!)
);

-- Insert JSON
INSERT INTO documents (metadata, data) VALUES 
(
    '{"name": "John", "age": 30, "active": true}',  -- JSON
    '{"name": "John", "age": 30, "active": true}'   -- JSONB
);

-- JSONB advantages:
-- 1. Faster querying
-- 2. Supports indexes (GIN indexes)
-- 3. Deduplicates keys
-- 4. More operators/functions

-- Use JSONB unless you need exact text preservation

2. JSONB Operators & Functions

sql

-- Access operators
SELECT 
    data -> 'name' as name_json,        -- Returns JSON
    data ->> 'name' as name_text,       -- Returns TEXT
    data -> 'tags' -> 0 as first_tag,   -- Nested access
    data #> '{address,city}' as city    -- Path access
FROM documents;

-- Check keys/values
SELECT * FROM documents WHERE
    data ? 'name',                      -- Has key
    data ?| array['name', 'email'],     -- Has any key
    data ?& array['name', 'email'],     -- Has all keys
    data @> '{"active": true}',         -- Contains
    data <@ '{"name": "John", "age": 30}';  -- Contained by

-- Modify JSONB
UPDATE documents SET 
    data = data || '{"email": "john@example.com"}',  -- Merge
    data = data - 'temp_field';                      -- Remove key

-- JSON functions
SELECT 
    jsonb_pretty(data),                 -- Pretty print
    jsonb_array_length(data->'tags'),   -- Array length
    jsonb_object_keys(data),            -- List keys
    jsonb_each(data)                    -- Key-value pairs
FROM documents;

3. JSONB Indexing (Performance!)

sql

-- GIN indexes for JSONB (Generalized Inverted Index)
CREATE INDEX idx_documents_data ON documents USING GIN (data);

-- Specific path indexes
CREATE INDEX idx_documents_name ON documents ((data ->> 'name'));

-- Query using index
SELECT * FROM documents WHERE data @> '{"active": true}';
-- This uses the GIN index!

-- Partial indexes on JSONB
CREATE INDEX idx_active_users ON documents ((data ->> 'name'))
WHERE data @> '{"active": true}';

H. Type Conversion & Casting

sql

-- Explicit casting
SELECT '123'::INTEGER;
SELECT 123::TEXT;
SELECT CURRENT_TIMESTAMP::DATE;

-- Using CAST function
SELECT CAST('123' AS INTEGER);
SELECT CAST(123 AS TEXT);

-- Implicit casting (where supported)
SELECT '123' + 456;  -- Works: '123' converted to integer

-- Type-specific functions
SELECT to_char(123.456, '999.99');
SELECT to_date('2024-01-15', 'YYYY-MM-DD');
SELECT to_timestamp('2024-01-15 14:30', 'YYYY-MM-DD HH24:MI');

I. Choosing the Right Type

sql

-- Primary keys:
--   SERIAL/BIGSERIAL - Simple, auto-increment
--   UUID - Distributed systems, security

-- Text data:
--   TEXT - Almost always
--   VARCHAR(n) - Only for constraint enforcement

-- Numbers:
--   INTEGER - Most integers
--   NUMERIC - Money, exact calculations
--   REAL/DOUBLE - Scientific, approximate

-- Dates:
--   TIMESTAMPTZ - ALWAYS for timestamps
--   DATE - Only dates

-- Special:
--   JSONB - Semi-structured data
--   ARRAY[] - When you need arrays
--   BOOLEAN - True/false flags

J. Custom Types & Domains

sql

-- Create custom type
CREATE TYPE address AS (
    street TEXT,
    city TEXT,
    postal_code VARCHAR(10),
    country CHAR(2)
);

CREATE TABLE customers (
    id SERIAL,
    home_address address
);

-- Create domain (constrained type)
CREATE DOMAIN email_address AS TEXT
    CHECK (VALUE ~ '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$');

CREATE TABLE users (
    email email_address  -- Now validated on insert!
);

PostgreSQL's rich type system is one of its greatest strengths, allowing you to model data more naturally and leverage database features for validation and performance.