Skip to content
Flowchart Learning

Data_types

Chapter 5: PostgreSQL Data Types

Section titled “Chapter 5: PostgreSQL Data Types”

Comprehensive Guide to Data Types in PostgreSQL

Section titled “Comprehensive Guide to Data Types in PostgreSQL”

5.1 Overview of Data Types

Section titled “5.1 Overview of Data Types”

PostgreSQL provides a rich set of data types that cover everything from basic types to complex structures. Understanding these types is essential for effective database design.

                    PostgreSQL Data Type Categories
    ========================================================================


    ┌─────────────────────────────────────────────────────────────────────┐
    │                    Data Type Overview                               │
    │                                                                      │
    │  ┌──────────────┐  ┌──────────────┐  ┌──────────────┐              │
    │  │   Numeric   │  │   Character  │  │   Date/Time │              │
    │  │              │  │              │  │              │              │
    │  │ INTEGER     │  │ VARCHAR      │  │ DATE         │              │
    │  │ BIGINT      │  │ CHAR         │  │ TIME         │              │
    │  │ SMALLINT    │  │ TEXT         │  │ TIMESTAMP    │              │
    │  │ DECIMAL     │  │              │  │ INTERVAL     │              │
    │  │ NUMERIC     │  │              │  │              │              │
    │  │ REAL        │  │              │  │              │              │
    │  │ DOUBLE      │  │              │  │              │              │
    └──────────────┘  └──────────────┘  └──────────────┘              │
    │                                                                      │
    │  ┌──────────────┐  ┌──────────────┐  ┌──────────────┐              │
    │  │   Boolean   │  │   Monetary   │  │    UUID     │              │
    │  │              │  │              │  │              │              │
    │  │ BOOLEAN     │  │ MONEY        │  │ UUID         │              │
    │  │              │  │              │  │              │              │
    │  └──────────────┘  └──────────────┘  └──────────────┘              │
    │                                                                      │
    │  ┌──────────────┐  ┌──────────────┐  ┌──────────────┐              │
    │  │    JSON     │  │    Array    │  │   Network   │              │
    │  │              │  │              │  │              │              │
    │  │ JSON         │  │ INTEGER[]   │  │ INET         │              │
    │  │ JSONB        │  │ TEXT[]      │  │ CIDR         │              │
    │  │              │  │ TIMESTAMP[]│  │ MACADDR      │              │
    │  └──────────────┘  └──────────────┘  └──────────────┘              │
    │                                                                      │
    │  ┌──────────────┐  ┌──────────────┐  ┌──────────────┐              │
    │  │    Geometric │  │   Bit String │  │   Custom    │              │
    │  │              │  │              │  │              │              │
    │  │ POINT        │  │ BIT          │  │ ENUM         │              │
    │  │ LINE         │  │ BIT VARYING  │  │ COMPOSITE    │              │
    │  │ POLYGON      │  │              │  │ RANGE        │              │
    │  │ CIRCLE       │  │              │  │ DOMAIN       │              │
    │  └──────────────┘  └──────────────┘  └──────────────┘              │
    │                                                                      │
    └─────────────────────────────────────────────────────────────────────┘
    ========================================================================

5.2 Numeric Types

Section titled “5.2 Numeric Types”

PostgreSQL provides several integer and floating-point types with different ranges and precision.

                    Numeric Data Types
    ========================================================================


    Integer Types:
    ==============


    ┌─────────────────────────────────────────────────────────────────────┐
    │  Type        │  Storage  │  Range                                │
    ├──────────────┼───────────┼────────────────────────────────────────┤
    │  SMALLINT    │  2 bytes  │  -32,768 to 32,767                     │
    ├──────────────┼───────────┼────────────────────────────────────────┤
    │  INTEGER     │  4 bytes  │  -2,147,483,648 to 2,147,483,647       │
    ├──────────────┼───────────┼────────────────────────────────────────┤
    │  BIGINT      │  8 bytes  │  -9,223,372,036,854,775,808 to         │
    │              │           │  9,223,372,036,854,775,807              │
    ├──────────────┼───────────┼────────────────────────────────────────┤
    │  SERIAL      │  4 bytes  │  1 to 2,147,483,647 (auto-increment)   │
    ├──────────────┼───────────┼────────────────────────────────────────┤
    │  BIGSERIAL   │  8 bytes  │  1 to 9,223,372,036,854,775,807        │
    └──────────────┴───────────┴────────────────────────────────────────┘


    Examples:
    =========


    CREATE TABLE products (
        id SERIAL PRIMARY KEY,           -- Auto-incrementing integer
        product_id BIGSERIAL,            -- For large systems
        quantity SMALLINT,               -- Small numbers (stock)
        price INTEGER,                   -- In cents (avoid floating point)
        views INTEGER DEFAULT 0,
        likes BIGINT                     -- For viral content
    );


    Numeric Types (Exact Precision):
    ================================


    ┌─────────────────────────────────────────────────────────────────────┐
    │  Type           │  Description                                     │
    ├─────────────────┼──────────────────────────────────────────────────┤
    │  NUMERIC(p,s)   │  Exact numeric, p=precision, s=scale            │
    │  NUMERIC        │  Up to 131072 digits before decimal             │
    │                 │  Up to 16383 digits after decimal               │
    ├─────────────────┼──────────────────────────────────────────────────┤
    │  DECIMAL(p,s)   │  Alias for NUMERIC                              │
    ├─────────────────┼──────────────────────────────────────────────────┤
    │  NUMBER(p,s)    │  Alias for NUMERIC (Oracle compatibility)      │
    └─────────────────┴──────────────────────────────────────────────────┘


    Examples:
    =========


    CREATE TABLE financial_data (
        amount DECIMAL(10,2),       -- 10 digits total, 2 after decimal
        rate DECIMAL(5,4),          -- 5 digits total, 4 after decimal
        balance NUMERIC(15,2),      -- For large monetary values
        percentage NUMERIC(5,2)      -- 0.00 to 100.00
    );


    Floating-Point Types (Approximate):
    ===================================


    ┌─────────────────────────────────────────────────────────────────────┐
    │  Type        │  Storage  │  Range                                 │
    ├──────────────┼───────────┼────────────────────────────────────────┤
    │  REAL        │  4 bytes  │  ±1E-37 to ±3.4E38 (6 decimal digits) │
    ├──────────────┼───────────┼────────────────────────────────────────┤
    │  DOUBLE      │  8 bytes  │  ±1E-307 to ±1.8E308 (15 decimal digits│
    │  PRECISION  │           │                                        │
    ├──────────────┼───────────┼────────────────────────────────────────┤
    │  FLOAT(n)    │  4 or 8   │  FLOAT(1-24) = REAL                    │
    │              │  bytes    │  FLOAT(25-53) = DOUBLE PRECISION      │
    └──────────────┴───────────┴────────────────────────────────────────┘


    Examples:
    =========


    CREATE TABLE measurements (
        temperature REAL,              -- For sensor readings
        latitude DOUBLE PRECISION,     -- For GPS coordinates
        weight FLOAT,                   -- General use
        score DOUBLE PRECISION         -- For calculations
    );


    Best Practices for Numeric Types:
    ==================================


    ┌─────────────────────────────────────────────────────────────────────┐
    │  Tip                        │  Recommendation                      │
    ├─────────────────────────────┼──────────────────────────────────────┤
    │  Money                      │  Use NUMERIC, not REAL               │
    │                             │  (floating point has rounding errors)│
    ├─────────────────────────────┼──────────────────────────────────────┤
    │  Primary Keys               │  Use SERIAL or BIGSERIAL            │
    ├─────────────────────────────┼──────────────────────────────────────┤
    │  Currency                   │  Store in cents (INTEGER)           │
    │                             │  Divide by 100 for display          │
    ├─────────────────────────────┼──────────────────────────────────────
    │  Scientific values          │  Use DOUBLE PRECISION
    └─────────────────────────────┴──────────────────────────────────────┘
    ```


---


## 5.3 Character Types


Character types store text data with various storage characteristics.
                Character Data Types
========================================================================


Character Types Overview:
=========================


┌─────────────────────────────────────────────────────────────────────┐
│  Type            │  Description                                   │
├──────────────────┼──────────────────────────────────────────────────┤
│  CHAR(n)         │  Fixed-length, padded with spaces               │
├──────────────────┼──────────────────────────────────────────────────┤
│  VARCHAR(n)     │  Variable-length with limit                     │
├──────────────────┼──────────────────────────────────────────────────┤
│  TEXT            │  Variable-length, no limit                      │
├──────────────────┼──────────────────────────────────────────────────┤
│  VARCHAR         │  Alias for VARCHAR(n), no limit                 │
└──────────────────┴──────────────────────────────────────────────────┘


Storage and Behavior:
=====================


┌─────────────────────────────────────────────────────────────────────┐
│  Type      │  Storage         │  Behavior                         │
├────────────┼──────────────────┼───────────────────────────────────┤
│  CHAR(4)   │  Fixed 4 bytes   │  Always 4 bytes, pads with spaces │
│            │                  │  'ab' → 'ab  '                   │
├────────────┼──────────────────┼───────────────────────────────────┤
│  VARCHAR(4)│  1-5 bytes + len │  Up to 4 characters              │
│            │                  │  'ab' → 'ab' (no padding)         │
├────────────┼──────────────────┼───────────────────────────────────┤
│  TEXT      │  1 byte + length │  No length limit                  │
│            │  (up to 1GB)     │  Best for general text            │
└────────────┴──────────────────┴───────────────────────────────────┘


Examples:
=========


CREATE TABLE users (
    -- Fixed-length for codes
    country_code CHAR(2),          -- 'US', 'GB', 'IN'
    state_code CHAR(2),            -- 'NY', 'CA'


    -- Variable with limit
    username VARCHAR(50),         -- Max 50 characters
    email VARCHAR(255),           -- Common email length
    phone VARCHAR(20),            -- Phone number with country code


    -- Unlimited text
    bio TEXT,                     -- User biography
    content TEXT,                 -- Blog post content
    description TEXT              -- Product description
);


Common String Functions:
========================


-- Length
SELECT LENGTH('hello');              -- 5
SELECT CHAR_LENGTH('hello');          -- 5


-- Case conversion
SELECT UPPER('hello');                -- 'HELLO'
SELECT LOWER('HELLO');                -- 'hello'
SELECT INITCAP('hello world');        -- 'Hello World'


-- Substring
SELECT SUBSTRING('hello world', 1, 5);  -- 'hello'
SELECT SUBSTRING('hello world', 7);     -- 'world'


-- Trim
SELECT TRIM('  hello  ');              -- 'hello'
SELECT LTRIM('  hello');               -- 'hello'
SELECT RTRIM('hello  ');              -- 'hello'


-- Concatenation
SELECT 'hello' || ' ' || 'world';     -- 'hello world'
SELECT CONCAT('hello', ' ', 'world'); -- 'hello world'
SELECT CONCAT_WS(' ', 'hello', 'world'); -- 'hello world'


-- String position
SELECT POSITION('world' IN 'hello world');  -- 7
SELECT STRPOS('hello world', 'world');     -- 7


-- Replace
SELECT REPLACE('hello world', 'world', 'postgres');  -- 'hello postgres'


-- Split
SELECT SPLIT_PART('a,b,c', ',', 2);  -- 'b'


-- Format
SELECT FORMAT('Hello %s', 'World');   -- 'Hello World'
SELECT FORMAT('Value: %s, Count: %s', 'test', 42);  -- 'Value: test, Count: 42'
```

5.4 Date and Time Types

Section titled “5.4 Date and Time Types”

PostgreSQL provides comprehensive date and time types for handling temporal data.

                    Date and Time Data Types
    ========================================================================


    Date/Time Types Overview:
    ========================


    ┌─────────────────────────────────────────────────────────────────────┐
    │  Type             │  Description              │  Range            │
    ├───────────────────┼───────────────────────────┼───────────────────┤
    │  DATE             │  Date only                │  4713 BC to 5874897│
    ├───────────────────┼───────────────────────────┼───────────────────┤
    │  TIME             │  Time only                │  00:00:00 to      │
    │                   │                           │  24:00:00         │
    ├───────────────────┼───────────────────────────┼───────────────────┤
    │  TIMESTAMP        │  Date + Time              │  4713 BC to       │
    │                   │  (no timezone)            │  294276 AD        │
    ├───────────────────┼───────────────────────────┼───────────────────┤
    │  TIMESTAMPTZ      │  Date + Time              │  4713 BC to       │
    │                   │  (with timezone)          │  294276 AD        │
    ├───────────────────┼───────────────────────────┼───────────────────┤
    │  INTERVAL         │  Duration                 │  -178000000 years │
    │                   │                           │  to 178000000     │
    └───────────────────┴───────────────────────────┴───────────────────┘


    Examples:
    =========


    CREATE TABLE events (
        event_date DATE,                    -- 2024-01-15
        start_time TIME,                    -- 14:30:00
        created_at TIMESTAMP,                -- 2024-01-15 14:30:00
        updated_at TIMESTAMPTZ,              -- 2024-01-15 14:30:00+05:30
        duration INTERVAL                    -- 2 hours 30 minutes
    );


    Current Date/Time Functions:
    ============================


    -- Get current date/time
    SELECT CURRENT_DATE;           -- 2024-01-15
    SELECT CURRENT_TIME;           -- 14:30:00+05:30
    SELECT CURRENT_TIMESTAMP;     -- 2024-01-15 14:30:00+05:30
    SELECT NOW();                 -- 2024-01-15 14:30:00+05:30 (TIMESTAMPTZ)
    SELECT LOCALTIME;             -- 14:30:00
    SELECT LOCALTIMESTAMP;        -- 2024-01-15 14:30:00


    Date/Time Operations:
    ====================


    -- Adding/subtracting intervals
    SELECT CURRENT_DATE + INTERVAL '7 days';           -- 7 days from now
    SELECT CURRENT_TIMESTAMP - INTERVAL '1 month';    -- 1 month ago
    SELECT '2024-01-15'::DATE + '3 weeks'::INTERVAL;   -- Date arithmetic


    -- Age calculation
    SELECT AGE('2024-01-15', '2020-01-15');            -- 4 years
    SELECT AGE('2024-01-15');                          -- Age from today


    -- Extracting parts
    SELECT EXTRACT(YEAR FROM CURRENT_DATE);           -- 2024
    SELECT EXTRACT(MONTH FROM CURRENT_DATE);          -- 1
    SELECT EXTRACT(DAY FROM CURRENT_DATE);            -- 15
    SELECT EXTRACT(DOW FROM CURRENT_DATE);             -- 1 (Monday)
    SELECT EXTRACT(QUARTER FROM CURRENT_DATE);         -- 1


    -- Truncating
    SELECT DATE_TRUNC('year', CURRENT_TIMESTAMP);      -- 2024-01-01 00:00:00
    SELECT DATE_TRUNC('month', CURRENT_TIMESTAMP);     -- 2024-01-01 00:00:00
    SELECT DATE_TRUNC('hour', CURRENT_TIMESTAMP);     -- 2024-01-15 14:00:00


    Date/Time Formatting:
    =====================


    -- To string (formatting)
    SELECT TO_CHAR(CURRENT_TIMESTAMP, 'YYYY-MM-DD');          -- 2024-01-15
    SELECT TO_CHAR(CURRENT_TIMESTAMP, 'Month DD, YYYY');       -- January 15, 2024
    SELECT TO_CHAR(CURRENT_TIMESTAMP, 'HH24:MI:SS');           -- 14:30:00
    SELECT TO_CHAR(CURRENT_TIMESTAMP, 'YYYY-MM-DD HH24:MI');   -- 2024-01-15 14:30


    Format Patterns:
    ===============


    ┌─────────────────────────────────────────────────────────────────────┐
    │  Pattern │  Description              │  Example Output           │
    ├──────────┼───────────────────────────┼───────────────────────────┤
    │  YYYY     │  4-digit year            │  2024                     │
    ├──────────┼───────────────────────────┼───────────────────────────┤
    │  YY       │  2-digit year            │  24                       │
    ├──────────┼───────────────────────────┼───────────────────────────┤
    │  MM       │  2-digit month (01-12)  │  01                       │
    ├──────────┼───────────────────────────┼───────────────────────────┤
    │  MONTH    │  Full month name          │  JANUARY                  │
    ├──────────┼───────────────────────────┼───────────────────────────┤
    │  DD       │  2-digit day             │  15                       │
    ├──────────┼───────────────────────────┼───────────────────────────┤
    │  HH24     │  Hour 00-23              │  14                       │
    ├──────────┼───────────────────────────┼───────────────────────────┤
    │  MI       │  Minutes                 │  30                       │
    ├──────────┼───────────────────────────┼───────────────────────────┤
    │  SS       │  Seconds                 │  00                       │
    ├──────────┼───────────────────────────┼───────────────────────────┤
    │  TZH      │  Timezone hours          │  +05                       │
    ├──────────┼───────────────────────────┼───────────────────────────┤
    │  TZM      │  Timezone minutes        │  30                       │
    └──────────┴───────────────────────────┴───────────────────────────┘


    -- From string (parsing)
    SELECT TO_DATE('2024-01-15', 'YYYY-MM-DD');       -- 2024-01-15
    SELECT TO_TIMESTAMP('2024-01-15 14:30',          -- 2024-01-15 14:30:00
                        'YYYY-MM-DD HH24:MI');


    Working with Timezones:
    ======================


    -- Set timezone
    SET TIMEZONE = 'America/New_York';
    SELECT NOW();                                    -- 2024-01-15 09:30:00-05:00


    SET TIMEZONE = 'UTC';
    SELECT NOW();                                    -- 2024-01-15 14:30:00+00:00


    -- Timezone conversion
    SELECT CURRENT_TIMESTAMP AT TIMEZONE 'America/New_York';
    SELECT '2024-01-15 14:30'::TIMESTAMPTZ AT TIMEZONE 'UTC';


    -- Using in tables
    CREATE TABLE log_entries (
        id SERIAL PRIMARY KEY,
        message TEXT,
        created_at TIMESTAMPTZ DEFAULT NOW()
    );


    -- Query with timezone
    SELECT * FROM log_entries
    WHERE created_at AT TIMEZONE 'America/New_York' = '2024-01-15';
    ```


---


## 5.5 Boolean Type


The boolean type stores true/false values.
                Boolean Data Type
========================================================================


Boolean Type:
=============


┌─────────────────────────────────────────────────────────────────────┐
│  Type      │  Storage  │  Values                                │
├────────────┼───────────┼─────────────────────────────────────────┤
│  BOOLEAN   │  1 byte   │  TRUE, FALSE, or NULL                 │
└────────────┴───────────┴─────────────────────────────────────────┘


Input Values:
=============


┌─────────────────────────────────────────────────────────────────────┐
│  True           │  False                                           │
├─────────────────┼───────────────────────────────────────────────────┤
│  true           │  false                                           │
├─────────────────┼───────────────────────────────────────────────────┤
│  't', 'true'    │  'f', 'false'                                    │
├─────────────────┼───────────────────────────────────────────────────┤
│  'yes', 'y'    │  'no', 'n'                                        │
├─────────────────┼───────────────────────────────────────────────────┤
│  '1', 'on'     │  '0', 'off'                                       │
├─────────────────┼───────────────────────────────────────────────────┤
│  TRUE          │  FALSE                                           │
└─────────────────┴───────────────────────────────────────────────────┘


Examples:
=========


CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50),
    is_active BOOLEAN DEFAULT TRUE,
    is_verified BOOLEAN DEFAULT FALSE,
    is_admin BOOLEAN
);


-- Insert boolean values
INSERT INTO users (username, is_active, is_verified)
VALUES
    ('john', true, 'yes'),
    ('jane', 't', '1'),
    ('admin', TRUE, 'true');


-- Query boolean values
SELECT * FROM users WHERE is_active = TRUE;
SELECT * FROM users WHERE is_active;
SELECT * FROM users WHERE NOT is_active;


Boolean Operations:
==================


-- AND, OR, NOT
SELECT TRUE AND FALSE;    -- FALSE
SELECT TRUE OR FALSE;     -- TRUE
SELECT NOT TRUE;          -- FALSE


-- In WHERE clause
SELECT * FROM users
WHERE is_active = TRUE AND is_verified = FALSE;


-- Using IS operator
SELECT * FROM users WHERE is_admin IS TRUE;
SELECT * FROM users WHERE is_admin IS NOT TRUE;  -- includes FALSE and NULL
```

5.6 UUID Type

Section titled “5.6 UUID Type”

Universally Unique Identifiers provide a way to generate globally unique IDs.

                    UUID Data Type
    ========================================================================


    UUID Type:
    ==========


    ┌─────────────────────────────────────────────────────────────────────┐
    │  Type      │  Storage  │  Format                                 │
    ├────────────┼───────────┼─────────────────────────────────────────┤
    │  UUID      │  16 bytes │  xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx  │
    └────────────┴───────────┴─────────────────────────────────────────┘


    Examples:
    =========


    CREATE TABLE sessions (
        id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
        user_id INTEGER,
        created_at TIMESTAMP DEFAULT NOW(),
        expires_at TIMESTAMP
    );


    -- Or use uuid-ossp extension for more functions
    CREATE EXTENSION IF NOT EXISTS "uuid-ossp";


    CREATE TABLE orders (
        id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
        user_id INTEGER,
        total DECIMAL(10,2)
    );


    Generating UUIDs:
    =================


    -- Using built-in function (PostgreSQL 13+)
    SELECT gen_random_uuid();      -- e.g., a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11


    -- Using uuid-ossp extension
    SELECT uuid_generate_v4();      -- Random UUID
    SELECT uuid_generate_v1();     -- Time-based UUID
    SELECT uuid_generate_v1mc();   -- Time-based with random multicast


    -- Generate from string
    SELECT 'a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11'::UUID;


    Converting UUIDs:
    ================


    -- UUID to text
    SELECT id::TEXT FROM orders;


    -- Text to UUID
    SELECT 'a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11'::UUID;


    -- Compare UUIDs
    SELECT * FROM orders WHERE id = 'a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11'::UUID;
    ```


---


## 5.7 JSON Types


PostgreSQL provides excellent support for JSON data with two types: JSON and JSONB.
                JSON Data Types
========================================================================


JSON vs JSONB:
==============


┌─────────────────────────────────────────────────────────────────────┐
│  Feature          │  JSON              │  JSONB                    │
├───────────────────┼────────────────────┼─────────────────────────┤
│  Storage          │  Stored as-is      │  Binary format          │
├───────────────────┼────────────────────┼─────────────────────────┤
│  Indexing         │  Limited            │  Full indexing support  │
├───────────────────┼────────────────────┼─────────────────────────┤
│  Query speed      │  Slower (parse)    │  Faster                  │
├───────────────────┼────────────────────┼─────────────────────────┤
│  Duplicate keys  │  Allowed            │  Last value wins        │
├───────────────────┼────────────────────┼─────────────────────────┤
│  Whitespace       │  Preserved         │  Removed                 │
├───────────────────┼────────────────────┼─────────────────────────┤
│  Use case         │  Quick storage     │  Querying and indexing  │
└───────────────────┴────────────────────┴─────────────────────────┘


Examples:
=========


CREATE TABLE user_profiles (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50),
    settings JSON,                    -- Flexible schema
    metadata JSONB                    -- For querying
);


-- Insert JSON data
INSERT INTO user_profiles (username, settings, metadata) VALUES
('john',
 '{"theme": "dark", "notifications": true, "language": "en"}',
 '{"browser": "Chrome", "last_login": "2024-01-15"}');


JSON Extraction Operators:
==========================


┌─────────────────────────────────────────────────────────────────────┐
│  Operator │  Description                     │  Returns        │
├───────────┼────────────────────────────────────┼─────────────────┤
│  ->       │  Get JSON object field            │  JSON           │
├───────────┼────────────────────────────────────┼─────────────────┤
│  ->>      │  Get JSON object field as text    │  TEXT           │
├───────────┼────────────────────────────────────┼─────────────────┤
│  #>       │  Get nested field (path)          │  JSON           │
├───────────┼────────────────────────────────────┼─────────────────┤
│  #>>      │  Get nested field as text         │  TEXT           │
└───────────┴────────────────────────────────────┴─────────────────┘


Examples:
=========


-- Get field
SELECT settings->'theme' FROM user_profiles;           -- "dark"
SELECT settings->>'theme' FROM user_profiles;           -- dark


-- Get nested field
SELECT metadata#>'{last_login}' FROM user_profiles;      -- "2024-01-15"
SELECT metadata#>>'{last_login}' FROM user_profiles;     -- 2024-01-15


JSON Functions:
===============


-- Get all keys
SELECT JSON_OBJECT_KEYS(settings) FROM user_profiles;


-- Number of keys
SELECT JSON_LENGTH(settings) FROM user_profiles;


-- Check if key exists
SELECT settings ? 'theme' FROM user_profiles;
SELECT settings ?| ARRAY['theme', 'language'] FROM user_profiles;
SELECT settings ?& ARRAY['theme', 'language'] FROM user_profiles;


-- Containment (for JSONB)
SELECT * FROM user_profiles
WHERE metadata @> '{"browser": "Chrome"}';


-- Has key at path
SELECT * FROM user_profiles
WHERE metadata @? '$.last_login';


-- Get array elements
INSERT INTO user_profiles (username, settings) VALUES
('jane', '{"tags": ["admin", "editor", "user"]}');


SELECT settings->'tags'->0 FROM user_profiles WHERE username = 'jane';  -- "admin"
SELECT settings->'tags'->>-1 FROM user_profiles WHERE username = 'jane'; -- "user"


JSON Path (JSONB):
==================


-- JSON path queries
SELECT metadata @? '$.browser' FROM user_profiles;           -- true/false
SELECT metadata @@ '$.browser == "Chrome"' FROM user_profiles;


-- Path with filter
SELECT * FROM user_profiles,
JSONB_ARRAY_ELEMENTS(settings->'tags') AS tag
WHERE tag ?| ARRAY['admin', 'editor'];


Indexing JSONB:
==============


-- GIN index for JSONB
CREATE INDEX idx_user_profiles_metadata ON user_profiles USING GIN(metadata);


-- Index specific path
CREATE INDEX idx_user_profiles_browser ON user_profiles ((metadata->>'browser'));


-- Using jsonb_path_ops for containment queries
CREATE INDEX idx_user_profiles_metadata_path
ON user_profiles USING GIN(metadata jsonb_path_ops);
```

5.8 Array Types

Section titled “5.8 Array Types”

PostgreSQL supports arrays of any built-in or user-defined type.

                    Array Data Types
    ========================================================================


    Array Type Declaration:
    ======================


    ┌─────────────────────────────────────────────────────────────────────┐
    │  Type                      │  Description                         │
    ├─────────────────────────────┼───────────────────────────────────────┤
    │  INTEGER[]                 │  Array of integers                   │
    ├─────────────────────────────┼───────────────────────────────────────┤
    │  TEXT[]                    │  Array of text                       │
    ├─────────────────────────────┼───────────────────────────────────────┤
    │  VARCHAR(255)[]            │  Array of varchar                    │
    ├─────────────────────────────┼───────────────────────────────────────┤
    │  TIMESTAMP[]              │  Array of timestamps                 │
    ├─────────────────────────────┼───────────────────────────────────────┤
    │  INTEGER ARRAY[3]         │  Fixed-size array (PostgreSQL 14+)  │
    └─────────────────────────────┴───────────────────────────────────────┘


    Examples:
    =========


    CREATE TABLE employees (
        id SERIAL PRIMARY KEY,
        name VARCHAR(100),
        skills TEXT[],                  -- Array of strings
        phone_numbers VARCHAR(20)[],    -- Fixed-size array
        project_ids INTEGER[],
        certifications TEXT[],          -- Can expand later
        availability BOOLEAN[]          -- Weekly availability
    );


    -- Insert array values
    INSERT INTO employees (name, skills, phone_numbers, project_ids) VALUES
    ('John Doe',
     ARRAY['Python', 'PostgreSQL', 'Docker'],
     ARRAY['+1-555-0100', '+1-555-0101'],
     ARRAY[1, 2, 3]);


    -- Alternative syntax
    INSERT INTO employees (name, skills)
    VALUES ('Jane Smith', '{"Java", "Spring Boot", "AWS"}');


    Array Operations:
    =================


    -- Access array element (1-based)
    SELECT skills[1] FROM employees;                    -- First skill


    -- Get array length
    SELECT ARRAY_LENGTH(skills, 1) FROM employees;    -- Number of skills


    -- Get all elements
    SELECT UNNEST(skills) FROM employees;              -- Expand to rows


    -- Check if contains
    SELECT * FROM employees WHERE skills @> ARRAY['Python'];
    SELECT * FROM employees WHERE 'Python' = ANY(skills);


    -- Check if overlaps
    SELECT * FROM employees WHERE skills && ARRAY['Python', 'Java'];


    -- Array concatenation
    SELECT ARRAY[1,2] || ARRAY[3,4];                    -- {1,2,3,4}
    SELECT 0 || ARRAY[1,2,3];                          -- {0,1,2,3}


    -- Array slicing
    SELECT skills[1:2] FROM employees;                -- First two elements


    Array Functions:
    ================


    -- Array aggregation
    SELECT ARRAY_AGG(name) FROM employees;


    -- Array to string
    SELECT ARRAY_TO_STRING(skills, ', ');              -- Python, PostgreSQL, Docker


    -- String to array
    SELECT STRING_TO_ARRAY('a,b,c', ',');              -- {a,b,c}


    -- Check for emptiness
    SELECT * FROM employees WHERE skills IS NULL OR skills = '{}';


    -- Array position
    SELECT ARRAY_POSITION(skills, 'Python') FROM employees;  -- 1


    Indexing Arrays:
    ================


    -- GIN index for arrays
    CREATE INDEX idx_employees_skills ON employees USING GIN(skills);


    -- Expression index
    CREATE INDEX idx_employees_first_skill ON employees ((skills[1]));
    ```


---


## 5.9 Network Address Types


PostgreSQL provides special types for IP addresses and network data.
                Network Address Data Types
========================================================================


Network Types:
==============


┌─────────────────────────────────────────────────────────────────────┐
│  Type      │  Description              │  Storage               │
├────────────┼────────────────────────────┼────────────────────────┤
│  INET      │  IPv4 or IPv6 address      │  7 or 19 bytes         │
├────────────┼────────────────────────────┼────────────────────────┤
│  CIDR      │  IPv4 or IPv6 network      │  7 or 19 bytes         │
├────────────┼────────────────────────────┼────────────────────────┤
│  MACADDR   │  MAC address               │  6 bytes                │
├────────────┼────────────────────────────┼────────────────────────┤
│  MACADDR8  │  MAC address (EUI-64)      │  8 bytes                │
└────────────┴────────────────────────────┴────────────────────────┘


Examples:
=========


CREATE TABLE servers (
    id SERIAL PRIMARY KEY,
    hostname VARCHAR(255),
    ip_address INET,
    ip_range CIDR,
    mac_address MACADDR
);


CREATE TABLE logs (
    id SERIAL PRIMARY KEY,
    source_ip INET,
    destination_ip INET,
    timestamp TIMESTAMPTZ DEFAULT NOW()
);


-- Insert network values
INSERT INTO servers (hostname, ip_address, ip_range, mac_address) VALUES
('web01', '192.168.1.10', '192.168.1.0/24', '08:00:2b:01:02:03'),
('db01', '10.0.0.5', '10.0.0.0/16', '08:00:2b:04:05:06');


Network Functions:
==================


-- Get network address
SELECT network('192.168.1.10/24');              -- 192.168.1.0/24


-- Get broadcast address
SELECT broadcast('192.168.1.10/24');            -- 192.168.1.255/24


-- Get netmask
SELECT netmask('192.168.1.10/24');              -- 255.255.255.0


-- Get host mask
SELECT hostmask('192.168.1.10/24');             -- 0.0.0.255


-- Get prefix length
SELECT masklen('192.168.1.0/24');               -- 24


-- Get first host
SELECT hostmin('192.168.1.0/24');               -- 192.168.1.1


-- Get last host
SELECT hostmax('192.168.1.0/24');              -- 192.168.1.254


Network Operators:
==================


┌─────────────────────────────────────────────────────────────────────┐
│  Operator │  Description                     │  Example            │
├───────────┼───────────────────────────────────┼─────────────────────┤
│  <<       │  Is contained by                  │ '10.0.0.0/8' << '0.0│
├───────────┼───────────────────────────────────┼─────────────────────┤
│  <<=      │  Is contained by or equals       │                     │
├───────────┼───────────────────────────────────┼─────────────────────┤
│  >>       │  Contains                         │ '0.0.0.0/0' >> '10'│
├───────────┼───────────────────────────────────┼─────────────────────┤
│  >>=      │  Contains or equals              │                     │
├───────────┼───────────────────────────────────┼─────────────────────┤
│  &&       │  Overlaps (network intersection) │ '10.0.0.0/8' && '10 │
├───────────┼───────────────────────────────────┼─────────────────────┤
│  =        │  Equals                           │                     │
├───────────┼───────────────────────────────────┼─────────────────────┤
│  <>       │  Not equals                       │                     │
└───────────┴───────────────────────────────────┴─────────────────────┘


Examples:
=========


-- Check if IP is in network
SELECT '192.168.1.10'::INET << '192.168.1.0/24'::CIDR;    -- true


-- Find all IPs in a range
SELECT * FROM logs
WHERE source_ip <<= '192.168.0.0/16'::CIDR;


-- Check for overlapping networks
SELECT * FROM servers
WHERE ip_range && '192.168.1.0/24'::CIDR;
```

5.10 Geometric Types

Section titled “5.10 Geometric Types”

PostgreSQL provides geometric data types for spatial data.

                    Geometric Data Types
    ========================================================================


    Geometric Types:
    ================


    ┌─────────────────────────────────────────────────────────────────────┐
    │  Type      │  Description              │  Storage                 │
    ├────────────┼────────────────────────────┼────────────────────────┤
    │  POINT     │  Point (x, y)             │  16 bytes                │
    ├────────────┼────────────────────────────┼────────────────────────┤
    │  LINE      │  Infinite line            │  32 bytes                │
    ├────────────┼────────────────────────────┼────────────────────────┤
    │  LSEG      │  Line segment             │  32 bytes                │
    ├────────────┼────────────────────────────┼────────────────────────┤
    │  BOX       │  Rectangle                │  32 bytes                │
    ├────────────┼────────────────────────────┼────────────────────────┤
    │  PATH      │  Closed or open path      │  16+ bytes               │
    ├────────────┼────────────────────────────┼────────────────────────┤
    │  POLYGON   │  Polygon                  │  40+ bytes               │
    ├────────────┼────────────────────────────┼────────────────────────┤
    │  CIRCLE    │  Circle                   │  24 bytes                │
    └────────────┴────────────────────────────┼────────────────────────┘


    Examples:
    =========


    CREATE TABLE locations (
        id SERIAL PRIMARY KEY,
        name VARCHAR(100),
        coordinates POINT,          -- (latitude, longitude)
        bounding_box BOX,
        area POLYGON,
        route PATH,
        coverage CIRCLE
    );


    -- Insert geometric values
    INSERT INTO locations (name, coordinates) VALUES
    ('Office', '(40.7128, -74.0060)'),
    ('Warehouse', POINT(40.7589, -73.9851));


    Geometric Functions:
    ====================


    -- Distance between points
    SELECT POINT '(0,0)' <-> POINT '(3,4)';         -- 5


    -- Area
    SELECT AREA(BOX '((0,0), (2,2))');               -- 4


    -- Perimeter
    SELECT PERIMETER(BOX '((0,0), (2,2))');          -- 8


    -- Center
    SELECT CENTER(BOX '((0,0), (2,2))');            -- (1,1)


    -- Check intersection
    SELECT '(0,0),(1,1)'::LSEG && '(0,1),(1,0)'::LSEG;  -- true


    Note: For advanced geospatial data, use PostGIS extension.
    ```


---


## 5.11 Best Practices
                Data Type Best Practices
========================================================================


1. Numeric Types
   ┌──────────────────────────────────────────────────────────────┐
   │  • Use NUMERIC/DECIMAL for money (not REAL/DOUBLE)         │
   │  • Use INTEGER for IDs, not floating point                 │
   │  • Use BIGINT for large systems                            │
   └──────────────────────────────────────────────────────────────┘


2. Character Types
   ┌──────────────────────────────────────────────────────────────┐
   │  • Use TEXT for unlimited length                            │
   │  • Use VARCHAR(n) when you need a limit                    │
   │  • Avoid CHAR unless fixed-length is required               │
   └──────────────────────────────────────────────────────────────┘


3. Date/Time Types
   ┌──────────────────────────────────────────────────────────────┐
   │  • Use TIMESTAMPTZ for timestamps (includes timezone)       │
   │  • Use TIMESTAMP without timezone for local time only      │
   │  • Always store UTC, convert for display                    │
   └──────────────────────────────────────────────────────────────┘


4. JSON/JSONB
   ┌──────────────────────────────────────────────────────────────┐
   │  • Use JSONB for querying and indexing                     │
   │  • Create GIN indexes for JSONB                            │
   │  • Use JSON only for storage, JSONB for operations         │
   └──────────────────────────────────────────────────────────────┘


5. Network Types
   ┌──────────────────────────────────────────────────────────────┐
   │  • Use INET for single addresses                            │
   │  • Use CIDR for networks                                     │
   │  • Use network operators for IP comparisons                │
   └──────────────────────────────────────────────────────────────┘
========================================================================
---


## Next Chapter


[Chapter 6: Creating Tables & Constraints](./03_data_types/06_tables_constraints.md)


---


*Last Updated: February 2026*