Vendor-Specific Types

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91

-- PostgreSQL JSON and JSONB examples
 
-- JSON vs JSONB
CREATE TABLE json_demo (
    id          SERIAL PRIMARY KEY,
    data_json   JSON,           -- Stores as text, preserves formatting
    data_jsonb  JSONB           -- Stores as binary, optimized for queries
);
 
INSERT INTO json_demo (data_json, data_jsonb) VALUES 
(
    '{"name": "Alice", "tags": ["admin", "verified"], "settings": {"theme": "dark"}}',
    '{"name": "Alice", "tags": ["admin", "verified"], "settings": {"theme": "dark"}}'
);
 
-- JSONB removes duplicate keys and doesn't preserve order
-- JSON preserves exact input
 
-- JSON/JSONB column in real table
CREATE TABLE products_flexible (
    product_id      SERIAL PRIMARY KEY,
    product_name    VARCHAR(200) NOT NULL,
    base_price      DECIMAL(10, 2) NOT NULL,
    attributes      JSONB DEFAULT '{}'          -- Flexible attributes
);
 
INSERT INTO products_flexible (product_name, base_price, attributes) VALUES
('Laptop Pro', 1299.99, '{
    "brand": "TechCorp",
    "specs": {
        "cpu": "Intel i7",
        "ram_gb": 16,
        "storage_gb": 512
    },
    "colors": ["silver", "black"],
    "wireless": true
}'),
('Running Shoes', 129.99, '{
    "brand": "SportMax",
    "sizes": [7, 8, 9, 10, 11],
    "material": "mesh",
    "waterproof": false
}');
 
-- Accessing JSON values
SELECT 
    product_name,
    attributes->>'brand' AS brand,                    -- Get as text
    attributes->'specs'->>'cpu' AS cpu,               -- Nested access
    attributes->'specs'->'ram_gb' AS ram,             -- Get as JSON
    (attributes->'specs'->>'ram_gb')::int AS ram_int  -- Cast to int
FROM products_flexible;
 
-- Filtering on JSON fields
SELECT * FROM products_flexible
WHERE attributes->>'brand' = 'TechCorp';
 
SELECT * FROM products_flexible
WHERE (attributes->'specs'->>'ram_gb')::int >= 16;
 
-- Check for key existence
SELECT * FROM products_flexible
WHERE attributes ? 'waterproof';           -- Has key 'waterproof'
 
SELECT * FROM products_flexible
WHERE attributes @> '{"wireless": true}';  -- Contains this JSON
 
-- Array operations in JSONB
SELECT * FROM products_flexible
WHERE attributes->'colors' ? 'silver';     -- Array contains 'silver'
 
-- GIN index for JSONB (enables fast @>, ?, ?& operators)
CREATE INDEX idx_product_attrs ON products_flexible USING GIN (attributes);
 
-- Partial index on specific JSON path
CREATE INDEX idx_brand ON products_flexible ((attributes->>'brand'));
 
-- Update JSON values
UPDATE products_flexible
SET attributes = jsonb_set(attributes, '{specs, ram_gb}', '32')
WHERE product_id = 1;
 
-- Add new key
UPDATE products_flexible
SET attributes = attributes || '{"on_sale": true}'
WHERE product_id = 1;
 
-- Remove key
UPDATE products_flexible
SET attributes = attributes - 'on_sale'
WHERE product_id = 1;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81

-- PostgreSQL array examples
 
-- Array column declaration
CREATE TABLE articles (
    article_id      SERIAL PRIMARY KEY,
    title           VARCHAR(200) NOT NULL,
    content         TEXT NOT NULL,
    tags            TEXT[] NOT NULL DEFAULT '{}',      -- Array of text
    ratings         INTEGER[] DEFAULT NULL              -- Array of integers
);
 
-- Array literals for insertion
INSERT INTO articles (title, content, tags, ratings) VALUES
('PostgreSQL Tips', 'Content here...', 
 ARRAY['database', 'postgresql', 'sql'], 
 ARRAY[5, 4, 5, 3]),
('Web Development', 'Content here...', 
 '{"javascript", "web", "frontend"}',    -- Alternative syntax
 ARRAY[4, 4, 5]);
 
-- Array access (1-indexed!)
SELECT 
    title,
    tags[1] AS first_tag,                 -- First element
    tags[2:3] AS slice,                   -- Slice [2, 3]
    array_length(tags, 1) AS tag_count    -- Length
FROM articles;
 
-- Array operators
SELECT * FROM articles WHERE 'postgresql' = ANY(tags);  -- Contains element
SELECT * FROM articles WHERE tags @> ARRAY['database']; -- Contains all
SELECT * FROM articles WHERE tags && ARRAY['web', 'api']; -- Overlaps (any match)
SELECT * FROM articles WHERE tags <@ ARRAY['database', 'sql', 'postgresql', 'tips'];  -- Contained by
 
-- Array functions
SELECT 
    title,
    array_to_string(tags, ', ') AS tags_string,        -- Join with delimiter
    unnest(tags) AS individual_tags               -- Expand to rows
FROM articles;
 
-- Unnest to work with individual elements
SELECT a.title, t.tag
FROM articles a, unnest(a.tags) AS t(tag)
WHERE t.tag LIKE 'post%';
 
-- Array aggregation (collect values into array)
SELECT 
    category,
    array_agg(product_name ORDER BY product_name) AS products
FROM products
GROUP BY category;
 
-- Array modification
UPDATE articles
SET tags = array_append(tags, 'featured')
WHERE article_id = 1;
 
UPDATE articles
SET tags = array_remove(tags, 'database')
WHERE article_id = 1;
 
UPDATE articles
SET tags = array_cat(tags, ARRAY['new1', 'new2'])  -- Concatenate
WHERE article_id = 1;
 
-- GIN index for array containment queries
CREATE INDEX idx_article_tags ON articles USING GIN (tags);
 
-- Now @>, &&, and <@ operators use the index
 
-- Multi-dimensional arrays (less common)
CREATE TABLE matrix_data (
    id      SERIAL PRIMARY KEY,
    matrix  INTEGER[][]           -- 2D array
);
 
INSERT INTO matrix_data (matrix) VALUES 
(ARRAY[[1, 2, 3], [4, 5, 6]]);    -- 2x3 matrix
 
SELECT matrix[1][2] FROM matrix_data;  -- Element at row 1, col 2

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59

-- PostgreSQL UUID examples
 
-- Enable UUID extension (required for generation functions)
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
-- Or use newer gen_random_uuid() from pgcrypto
 
-- UUID column
CREATE TABLE users_uuid (
    user_id         UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    username        VARCHAR(50) NOT NULL,
    email           VARCHAR(255) NOT NULL UNIQUE
);
 
-- Insertion with auto-generated UUID
INSERT INTO users_uuid (username, email)
VALUES ('alice', 'alice@example.com');
 
-- Insertion with explicit UUID
INSERT INTO users_uuid (user_id, username, email)
VALUES ('550e8400-e29b-41d4-a716-446655440000', 'bob', 'bob@example.com');
 
-- UUID generation functions
SELECT 
    gen_random_uuid() AS random_uuid,            -- v4, recommended
    uuid_generate_v4() AS v4_uuid,               -- v4, from extension
    uuid_generate_v1() AS v1_uuid,               -- v1 (timestamp + MAC)
    uuid_generate_v5(uuid_ns_url(), 'example.com') AS v5_uuid;  -- Deterministic
 
-- UUID in foreign keys
CREATE TABLE orders_uuid (
    order_id        UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    customer_id     UUID NOT NULL REFERENCES users_uuid(user_id),
    order_date      DATE NOT NULL DEFAULT CURRENT_DATE
);
 
-- Querying by UUID
SELECT * FROM users_uuid 
WHERE user_id = '550e8400-e29b-41d4-a716-446655440000';
 
-- UUID comparison (lexicographic on bytes)
SELECT * FROM users_uuid ORDER BY user_id;  -- Not temporal order for v4!
 
-- MySQL UUID
-- MySQL has UUID functions but stores as CHAR(36) or BINARY(16)
-- CREATE TABLE mysql_uuid (
--     id BINARY(16) PRIMARY KEY DEFAULT (UUID_TO_BIN(UUID(), 1)),
--     ...
-- );
-- UUID_TO_BIN with swap_flag=1 improves index locality
 
-- SQL Server
-- Uses UNIQUEIDENTIFIER type (16 bytes)
-- NEWID() generates random, NEWSEQUENTIALID() generates sequential
 
-- Performance consideration: UUID v4 causes random index insertions
-- Solutions:
-- 1. Use UUID v7 (timestamp-prefixed, sortable) - emerging standard
-- 2. Use ULID (Universally Unique Lexicographically Sortable Identifier)
-- 3. Accept the overhead for the benefits (usually acceptable)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74

-- PostgreSQL network type examples
 
CREATE TABLE network_logs (
    log_id          SERIAL PRIMARY KEY,
    client_ip       INET NOT NULL,
    server_ip       INET NOT NULL,
    client_mac      MACADDR,
    logged_at       TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);
 
CREATE TABLE ip_allowlist (
    allowlist_id    SERIAL PRIMARY KEY,
    network         CIDR NOT NULL,            -- Network range
    description     VARCHAR(200)
);
 
-- Inserting network data
INSERT INTO network_logs (client_ip, server_ip, client_mac)
VALUES 
    ('192.168.1.100', '10.0.0.5', '08:00:2b:01:02:03'),
    ('2001:db8::1', '2001:db8::2', NULL),  -- IPv6
    ('192.168.1.150/32', '10.0.0.5', '08:00:2b:01:02:04');
 
INSERT INTO ip_allowlist (network, description)
VALUES 
    ('192.168.1.0/24', 'Office network'),
    ('10.0.0.0/8', 'Internal networks'),
    ('2001:db8::/32', 'IPv6 documentation range');
 
-- Containment operators
SELECT * FROM network_logs
WHERE client_ip << '192.168.0.0/16';  -- Contained in network
 
SELECT * FROM network_logs
WHERE client_ip <<= '192.168.1.0/24';  -- Contained in or equal
 
-- Check if IP is in allowlist
SELECT l.*, a.description
FROM network_logs l
JOIN ip_allowlist a ON l.client_ip << a.network;
 
-- Network functions
SELECT 
    client_ip,
    host(client_ip) AS ip_only,           -- Strip netmask
    masklen(client_ip) AS mask_length,    -- Get mask length
    network(client_ip) AS network_addr,   -- Network address
    broadcast(client_ip) AS broadcast_addr
FROM network_logs;
 
-- IP address arithmetic
SELECT 
    '192.168.1.0/24'::CIDR + 5 AS fifth_ip,     -- Add offset
    '192.168.1.100'::INET - '192.168.1.1'::INET AS difference,  -- Difference
    set_masklen('192.168.1.100'::INET, 24) AS with_mask;
 
-- Sorting IPs (proper numeric order, not lexicographic)
SELECT client_ip FROM network_logs ORDER BY client_ip;
-- Returns: 10.0.0.5, 192.168.1.100, 192.168.1.150 (correct order)
-- VARCHAR would give: 10..., 192.168.1.1..., 192.168.1.5... (wrong!)
 
-- GIST index for network containment
CREATE INDEX idx_allowlist_network ON ip_allowlist USING GIST (network inet_ops);
 
-- MAC address operations
SELECT 
    client_mac,
    trunc(client_mac) AS manufacturer_id  -- First 3 bytes (OUI)
FROM network_logs
WHERE client_mac IS NOT NULL;
 
-- Validate IP on insert (built-in with type)
INSERT INTO network_logs (client_ip, server_ip)
VALUES ('999.999.999.999', '10.0.0.1');  -- ERROR: invalid input syntax

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86

-- PostgreSQL built-in geometric types
 
CREATE TABLE locations (
    location_id     SERIAL PRIMARY KEY,
    name            VARCHAR(100) NOT NULL,
    position        POINT NOT NULL              -- (longitude, latitude)
);
 
CREATE TABLE zones (
    zone_id         SERIAL PRIMARY KEY,
    zone_name       VARCHAR(100) NOT NULL,
    boundary        POLYGON NOT NULL
);
 
-- Insert geometric data
INSERT INTO locations (name, position) VALUES
    ('Times Square', POINT(-73.9857, 40.7580)),
    ('Central Park', POINT(-73.9654, 40.7829)),
    ('Empire State', POINT(-73.9857, 40.7484));
 
INSERT INTO zones (zone_name, boundary) VALUES
    ('Service Area', POLYGON '((-74.0, 40.7), (-74.0, 40.8), (-73.9, 40.8), (-73.9, 40.7), (-74.0, 40.7))');
 
-- Basic geometric operations
SELECT 
    name,
    position,
    position[0] AS longitude,             -- X coordinate
    position[1] AS latitude               -- Y coordinate
FROM locations;
 
-- Distance calculation (Euclidean, not Earth distance!)
SELECT 
    l1.name AS from_loc,
    l2.name AS to_loc,
    l1.position <-> l2.position AS distance    -- <-> is distance operator
FROM locations l1, locations l2
WHERE l1.location_id < l2.location_id;
 
-- Containment: point in polygon
SELECT * FROM locations l, zones z
WHERE l.position <@ z.boundary;               -- Point contained in polygon
 
-- GIST index for geometric operations
CREATE INDEX idx_locations_pos ON locations USING GIST (position);
CREATE INDEX idx_zones_boundary ON zones USING GIST (boundary);
 
---------------------------------------------
-- PostGIS for real geospatial applications
---------------------------------------------
 
-- Enable PostGIS extension
-- CREATE EXTENSION postgis;
 
-- GEOGRAPHY type (spherical, uses lat/lon)
CREATE TABLE stores (
    store_id        SERIAL PRIMARY KEY,
    store_name      VARCHAR(100) NOT NULL,
    location        GEOGRAPHY(POINT, 4326)    -- SRID 4326 = WGS84 (GPS)
);
 
-- Insert as geography
INSERT INTO stores (store_name, location) VALUES
    ('NYC Store', ST_MakePoint(-73.9857, 40.7580)::geography),
    ('LA Store', ST_MakePoint(-118.2437, 34.0522)::geography);
 
-- Distance in meters (real Earth distance!)
SELECT 
    a.store_name,
    b.store_name,
    ST_Distance(a.location, b.location) AS distance_meters,
    ST_Distance(a.location, b.location) / 1000 AS distance_km
FROM stores a, stores b
WHERE a.store_id < b.store_id;
 
-- Find stores within 10km of a point
SELECT store_name
FROM stores
WHERE ST_DWithin(
    location,
    ST_MakePoint(-73.9857, 40.7580)::geography,
    10000  -- 10 km in meters
);
 
-- Spatial index for geography
CREATE INDEX idx_stores_location ON stores USING GIST (location);

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81

-- PostgreSQL BYTEA examples
 
CREATE TABLE attachments (
    attachment_id   SERIAL PRIMARY KEY,
    file_name       VARCHAR(255) NOT NULL,
    mime_type       VARCHAR(100) NOT NULL,
    file_size       INTEGER NOT NULL,
    file_data       BYTEA NOT NULL,
    uploaded_at     TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);
 
-- Insert binary data (hex format)
INSERT INTO attachments (file_name, mime_type, file_size, file_data)
VALUES ('icon.png', 'image/png', 100, '\x89504e470d0a1a0a...');
 
-- Insert from application (parameterized)
-- In application code:
-- cursor.execute("INSERT INTO attachments ... VALUES (%s, %s, %s, %s)",
--                [filename, mimetype, len(data), psycopg2.Binary(data)])
 
-- Retrieve binary data
SELECT file_name, mime_type, file_size, file_data
FROM attachments
WHERE attachment_id = 1;
 
-- Get metadata without loading binary
SELECT file_name, mime_type, file_size, uploaded_at,
       length(file_data) AS actual_size  -- Verify size
FROM attachments;
 
-- Hash of binary content (for deduplication)
CREATE TABLE unique_files (
    file_hash       BYTEA PRIMARY KEY,        -- SHA-256 hash
    file_data       BYTEA NOT NULL,
    reference_count INTEGER DEFAULT 1
);
 
INSERT INTO unique_files (file_hash, file_data)
VALUES (sha256('file content'), 'file content');
 
-- Encryption patterns
-- Store encrypted data, key managed separately
CREATE TABLE encrypted_data (
    data_id         SERIAL PRIMARY KEY,
    encrypted_value BYTEA NOT NULL,
    iv              BYTEA NOT NULL,           -- Initialization vector
    key_id          VARCHAR(50) NOT NULL      -- Reference to key management
);
 
-- PostgreSQL pgcrypto extension for encryption
-- CREATE EXTENSION pgcrypto;
-- INSERT INTO encrypted_data (encrypted_value, iv, key_id)
-- VALUES (
--     pgp_sym_encrypt('sensitive data', 'encryption_key'),
--     gen_random_bytes(16),
--     'key_2024_01'
-- );
 
-- MySQL BLOB types
-- TINYBLOB: 255 bytes
-- BLOB: 64 KB
-- MEDIUMBLOB: 16 MB
-- LONGBLOB: 4 GB
 
-- SQL Server
-- VARBINARY(n): Up to 8000 bytes
-- VARBINARY(MAX): Up to 2 GB
-- With FILESTREAM: Store in filesystem with DB transaction support
 
-- File reference pattern (recommended for large files)
CREATE TABLE documents (
    document_id     SERIAL PRIMARY KEY,
    file_name       VARCHAR(255) NOT NULL,
    storage_path    VARCHAR(500) NOT NULL,    -- S3 key or filesystem path
    storage_bucket  VARCHAR(100),             -- S3 bucket
    file_hash       BYTEA,                    -- For integrity verification
    mime_type       VARCHAR(100) NOT NULL,
    file_size       BIGINT NOT NULL,
    uploaded_at     TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);
-- Store metadata in DB, file in object storage