PostgreSQL Deep Dive Part 6 - Money, Type Casting, and Boolean Operations

CREATE TABLE money_example (
    item_name TEXT,
    price MONEY
);
 
INSERT INTO money_example (item_name, price) VALUES
    ('Laptop', 1999.99),
    ('Smartphone', 799),
    ('Pen', .25),
    ('Headphone', '$199.99'),
    ('Smartwatch', 249.75),
    ('Gaming Console', 299.95);
 
SELECT * FROM money_example;
 
--| item_name      | price     |
--|----------------|-----------|
--| Laptop         | $1,999.99 |
--| Smartphone     |   $799.00 |
--| Pen            |     $0.25 |
--| Headphone      |   $199.99 |
--| Smartwatch     |   $249.75 |
--| Gaming Console |   $299.95 |

SHOW lc_monetary;-- Check current currency setting
 
--| lc_monetary |
--|-------------|
--| C           |
 
SET lc_monetary = 'en_GB.UTF-8';
-- Change to British Pounds-- The display changes but the underlying value remains in the original currency
 
SELECT * FROM money_example;
 
--| item_name      | price     |
--|----------------|-----------|
--| Laptop         | £1,999.99 |
--| Smartphone     |   £799.00 |
--| Pen            |     £0.25 |
--| Headphone      |   £199.99 |
--| Smartwatch     |   £249.75 |
--| Gaming Console |   £299.95 |

-- Money type rounds after two decimal places
INSERT INTO money_example VALUES ('Bitcoin', 29876.123456);
-- Rounds to 29876.12

CREATE TABLE financial_transactions (
    id SERIAL PRIMARY KEY,
    description TEXT,
    amount_cents INTEGER NOT NULL,
    currency_code CHAR(3) NOT NULL
);
 
-- Converting dollars to cents
SELECT (100.78 * 100)::INTEGER as cents_value;
 
-- For display
SELECT
    description,
    amount_cents::NUMERIC / 100 as amount_dollars,
    currency_code
FROM financial_transactions;

CREATE TABLE precise_financial (
    id SERIAL PRIMARY KEY,
    amount NUMERIC(19,4),
    currency_code CHAR(3),
 
-- Enforce precision rules
    CONSTRAINT valid_amount
        CHECK (scale(amount) <= 4)
);
 
-- Handle different precision requirements
INSERT INTO precise_financial (amount, currency_code) VALUES
    (1234.5678, 'USD'),-- 4 decimal places for USD
    (1234.56, 'EUR'),-- 2 decimal places for EUR
    (1234.567, 'BTC');-- 3 decimal places for Bitcoin

-- Double-colon syntax
SELECT
    100::MONEY,-- $100.00
    '2024-01-01'::DATE,  -- 2024-01-01
    '123.45'::NUMERIC;   -- 123.45
 
-- CAST function
SELECT
    CAST(100 AS MONEY),
    CAST('2024-01-01' AS DATE),
    CAST('123.45' AS NUMERIC);
 
-- Type inspection
SELECT
    pg_typeof(100::INT8),-- bigint
    pg_typeof(CAST(100 AS INT4)),-- integer
    pg_typeof('123.45'::NUMERIC(10,2));-- numeric

-- Compare storage requirements
SELECT
-- Integer sizes
    pg_column_size(100::INT2) as smallint_size,-- 2
    pg_column_size(100::INT4) as integer_size,-- 4
    pg_column_size(100::INT8) as bigint_size,-- 8
 
-- Numeric sizes vary with content
    pg_column_size(1000.12313::NUMERIC) as small_numeric,-- 8
    pg_column_size(1000313123.12313::NUMERIC) as medium_numeric,-- 22
    pg_column_size(10002432523523552.12313::NUMERIC) as large_numeric;-- 44

-- Array casting
SELECT ARRAY[1,2,3]::TEXT[];  -- Converts integer array to text array
 
-- JSON casting
SELECT
    '{"key": "value"}'::JSON,
    '{"number": 123}'::JSONB;
 
-- Complex type casting
SELECT
-- UUID from text
    'a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11'::UUID,
 
-- Array from string
    '{1,2,3}'::INTEGER[],
 
-- Custom type casting
    ROW(1, 'test')::custom_type;

CREATE TABLE boolean_example (
    id SERIAL PRIMARY KEY,
    status BOOLEAN
);
 
-- PostgreSQL accepts various boolean representations
INSERT INTO boolean_example (status) VALUES
    (TRUE),-- TRUE
    (FALSE),-- FALSE
    ('t'),-- TRUE
    ('true'),-- TRUE
    ('f'),-- FALSE
    ('false'),-- FALSE
    ('1'),-- TRUE
    ('0'),-- FALSE
    ('on'),-- TRUE
    ('off'),-- FALSE
    ('yes'),-- TRUE
    ('no'),-- FALSE
    (NULL);-- NULL (unknown)

-- Three-valued logic with NULL
SELECT
    TRUE AND NULL,-- NULL
    FALSE AND NULL,-- FALSE
    TRUE OR NULL,-- TRUE
    FALSE OR NULL,-- NULL
    NOT NULL;-- NULL
 
-- Boolean aggregation
SELECT
    bool_and(status) as all_true,-- TRUE if all non-null values are TRUE
    bool_or(status) as any_true,-- TRUE if any value is TRUE
    count(*) FILTER (WHERE status) as true_count
FROM boolean_example;
 
--| all_true | any_true | true_count |
--|----------|----------|------------|
--| f        | t        |          6 |
 
-- Using booleans in constraints
CREATE TABLE inventory_items (
    id SERIAL PRIMARY KEY,
    name TEXT,
    in_stock BOOLEAN DEFAULT TRUE,
    discontinued BOOLEAN DEFAULT FALSE,
 
-- Logical constraints
    CONSTRAINT valid_status
        CHECK (
-- Can't be in stock if discontinued
            NOT (discontinued AND in_stock)
        )
);
 

-- Boolean takes just 1 byte
SELECT pg_column_size(TRUE::BOOLEAN);-- 1-- Compare with alternatives
SELECT
    pg_column_size('Y'::CHAR(1)),-- 1
    pg_column_size(1::SMALLINT),-- 2
    pg_column_size('true'::TEXT);-- 5