Built-in Functions — String, Date and Math Operations

PostgreSQL’s built-in functions eliminate the need to retrieve raw data and process it in Python. Instead of fetching a timestamp from the database and reformatting it in Python, use TO_CHAR(created_at, 'YYYY-MM-DD') in the query. Instead of fetching a full body column and taking the first 200 characters in Python, use LEFT(body, 200) in SQL. Pushing processing into the database reduces network data transfer, eliminates a Python processing pass, and often produces more efficient query plans. This lesson covers the PostgreSQL functions you will use most often in FastAPI applications.

String Functions #

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

-- Trimming whitespace
SELECT TRIM('  hello  '),  LTRIM('  hello'),  RTRIM('hello  ');
-- 'hello',  'hello',  'hello'

-- Length
SELECT LENGTH('hello'),  OCTET_LENGTH('hello');   -- 5, 5 (for ASCII)

-- Substring / extraction
SELECT SUBSTRING('hello world' FROM 7 FOR 5);   -- 'world'
SELECT LEFT('hello world', 5);                  -- 'hello'
SELECT RIGHT('hello world', 5);                 -- 'world'

-- Replace and split
SELECT REPLACE('hello world', 'world', 'there');   -- 'hello there'
SELECT SPLIT_PART('a,b,c', ',', 2);                -- 'b'  (1-indexed)
SELECT REGEXP_REPLACE('Hello World', '\s+', '-', 'g');  -- 'Hello-World'

-- Concatenation
SELECT CONCAT('Hello', ' ', 'World');              -- 'Hello World'
SELECT 'Hello' || ' ' || 'World';                  -- same result

-- Padding
SELECT LPAD('42', 5, '0');     -- '00042'
SELECT RPAD('hello', 10, '.');  -- 'hello.....'

-- Practical: generate a slug from a title
SELECT LOWER(REGEXP_REPLACE(TRIM(title), '[^a-zA-Z0-9]+', '-', 'g'))
FROM posts;

Date and Time Functions #

-- Current date and time
SELECT NOW(),  CURRENT_TIMESTAMP,  CURRENT_DATE,  CURRENT_TIME;

-- Truncate to a unit
SELECT DATE_TRUNC('month',  NOW());   -- 2025-08-01 00:00:00+00
SELECT DATE_TRUNC('week',   NOW());   -- Monday of current week
SELECT DATE_TRUNC('day',    NOW());   -- today at midnight
SELECT DATE_TRUNC('hour',   NOW());   -- start of current hour

-- Extract a component
SELECT DATE_PART('year',    NOW());   -- 2025
SELECT DATE_PART('month',   NOW());   -- 8
SELECT DATE_PART('day',     NOW());   -- 6
SELECT DATE_PART('hour',    NOW());   -- 14
SELECT DATE_PART('dow',     NOW());   -- 0=Sunday, 1=Monday, ..., 6=Saturday

-- Date arithmetic with INTERVAL
SELECT NOW() + INTERVAL '7 days';
SELECT NOW() - INTERVAL '30 minutes';
SELECT NOW() + INTERVAL '1 year 3 months';

-- Age — the duration between two timestamps
SELECT AGE(NOW(), '1990-06-15');                    -- 35 years 1 mon 21 days ...
SELECT AGE(expiry_date, NOW()) AS time_until_expiry FROM tokens;

-- Formatting dates for display
SELECT TO_CHAR(NOW(), 'YYYY-MM-DD');             -- '2025-08-06'
SELECT TO_CHAR(NOW(), 'Month DD, YYYY');          -- 'August   06, 2025'
SELECT TO_CHAR(NOW(), 'YYYY-MM-DD HH24:MI:SS');  -- '2025-08-06 14:30:00'

-- Convert timezone
SELECT NOW() AT TIME ZONE 'America/New_York';
SELECT NOW() AT TIME ZONE 'Asia/Kolkata';

Numeric Functions #

-- Rounding
SELECT ROUND(3.14159, 2);     -- 3.14
SELECT ROUND(3.5);            -- 4    (rounds to even in .5 cases)
SELECT CEIL(3.2);             -- 4    (ceiling: round up)
SELECT FLOOR(3.9);            -- 3    (floor: round down)
SELECT TRUNC(3.9);            -- 3    (truncate: remove decimal, don't round)

-- Absolute value and sign
SELECT ABS(-42);              -- 42
SELECT SIGN(-5), SIGN(0), SIGN(3);  -- -1, 0, 1

-- Power and square root
SELECT POWER(2, 10);          -- 1024
SELECT SQRT(144);             -- 12

-- Division (integer vs float)
SELECT 7 / 2;                 -- 3   (integer division truncates)
SELECT 7.0 / 2;               -- 3.5 (float division)
SELECT 7 / 2.0;               -- 3.5
SELECT CAST(7 AS FLOAT) / 2;  -- 3.5

-- Modulo (remainder)
SELECT 17 % 5;                -- 2
SELECT MOD(17, 5);            -- 2

-- Practical: percentage calculation
SELECT
    status,
    COUNT(*) AS count,
    ROUND(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER (), 1) AS percentage
FROM posts
GROUP BY status;

Common Mistakes #

Mistake 1 — Integer division when float is needed #

❌ Wrong — integer division truncates:

SELECT view_count / total_posts AS avg;   -- both INT → integer division, truncates!

✅ Correct — cast to float:

SELECT view_count::FLOAT / total_posts AS avg;   -- ✓ float division

Mistake 2 — Using DATE_PART instead of DATE_TRUNC for grouping #

❌ Wrong — groups August across ALL years together:

GROUP BY DATE_PART('month', created_at)   -- month 8 includes Aug 2024 and Aug 2025!

✅ Correct — truncate preserves full date context:

GROUP BY DATE_TRUNC('month', created_at)   -- ✓ '2025-08-01' vs '2024-08-01'

Mistake 3 — Regex replace without ‘g’ flag only replaces first occurrence #

❌ Wrong — only first space replaced:

SELECT REGEXP_REPLACE('hello world foo', '\s', '-');   -- 'hello-world foo'

✅ Correct — use ‘g’ flag for all occurrences:

SELECT REGEXP_REPLACE('hello world foo', '\s', '-', 'g');   -- 'hello-world-foo' ✓

Quick Reference #