GriffsNotes — PostgreSQL Functions

Concatenation

|| operator

stringNULL-sensitive

Concatenates two or more text values. Returns NULL if any operand is NULL.

SELECT 'Hello' || ' ' || 'World';
-- 'Hello World'

SELECT 'Hi' || NULL;
-- NULL  ← entire result is NULL

CONCAT(val1, val2, ...)

stringNULL-safe

Concatenates values. Treats NULL as an empty string — unlike ||.

SELECT CONCAT('Hello', ' ', 'World');
-- 'Hello World'

SELECT CONCAT('Hi', NULL, '!');
-- 'Hi!'  ← NULL silently skipped

Expression	Result	Why
`'a' \|\| NULL`	NULL	NULL propagates
`CONCAT('a', NULL)`	'a'	NULL treated as ''
`CONCAT_WS('-', 'a', NULL, 'b')`	'a-b'	NULL entries skipped entirely

Bonus: CONCAT_WS(separator, val1, val2, ...) joins non-NULL values with a separator.

Case & Length

LENGTH(text)

string

Returns the number of characters (not bytes) in a string.

SELECT LENGTH('hello'); -- 5
SELECT LENGTH(NULL);    -- NULL
-- CHAR_LENGTH() is identical

LOWER(text)

string

Converts all characters to lowercase.

SELECT LOWER('Hello WORLD');
-- 'hello world'

UPPER(text)

string

Converts all characters to uppercase.

SELECT UPPER('Hello World');
-- 'HELLO WORLD'

INITCAP(text)

stringpg-specific

Capitalises the first letter of each word; lowercases the rest.

SELECT INITCAP('hello world');
-- 'Hello World'

SELECT INITCAP('JOHN DOE');
-- 'John Doe'

LPAD / RPAD

string

Pad string to a given length on the left or right with a fill character.

LPAD('42', 5, '0')  → '00042'
RPAD('hi', 5, '.')  → 'hi...'

REVERSE(text)

string

Returns the string with characters in reverse order.

SELECT REVERSE('hello');
-- 'olleh'

SELECT REPEAT('ab', 3);
-- 'ababab'

Trimming Whitespace

LTRIM(text [, chars])

string

Removes leading characters (default: spaces) from the left.

LTRIM('  hi  ')       → 'hi  '
LTRIM('xxHello', 'x') → 'Hello'

RTRIM(text [, chars])

string

Removes trailing characters (default: spaces) from the right.

RTRIM('  hi  ')       → '  hi'
RTRIM('Hello!!', '!') → 'Hello'

TRIM([BOTH|LEADING|TRAILING] [chars] FROM text)

string

Full-featured trim. Removes from both ends by default.

TRIM('  hi  ')             → 'hi'
TRIM(LEADING  'x' FROM 'xxhi') → 'hi'
TRIM(TRAILING '!' FROM 'hi!')  → 'hi'

Extracting Substrings

SUBSTRING(text, start, length)

string1-based index

Extracts length characters starting at start. Indexing starts at 1 (not 0).

SELECT SUBSTRING('Hello World', 7, 5);
-- 'World'  (start=7, length=5)

SELECT SUBSTR('abcdef', 2, 3);
-- 'bcd'

Note: SUBSTR() is an alias. SQL standard syntax: SUBSTRING(text FROM 7 FOR 5).

LEFT(text, n) & RIGHT(text, n)

string

Returns the first or last n characters of a string. Negative n omits that many characters from the opposite end.

LEFT('Hello', 3)   → 'Hel'
RIGHT('Hello', 3)  → 'llo'
LEFT('Hello', -2)  → 'Hel' (skip last 2)
RIGHT('Hello', -2) → 'llo' (skip first 2)

Searching & Replacing

POSITION(substr IN text)

string1-based index

Returns the starting position of the first occurrence of substr in text. Returns 0 if not found.

SELECT POSITION('World' IN 'Hello World');
-- 7

SELECT POSITION('xyz' IN 'Hello');
-- 0  (not found)

-- STRPOS(text, substr) is equivalent:
STRPOS('Hello World', 'World') → 7

REPLACE(string, old, new)

string

Replaces all occurrences of old substring with new. Case-sensitive.

SELECT REPLACE('hello world', 'world', 'SQL');
-- 'hello SQL'

SELECT REPLACE('aabbcc', 'b', 'X');
-- 'aaXXcc'  (all occurrences)

-- To remove a substring, use '' as new:
REPLACE('hello!!', '!', '') → 'hello'

TRANSLATE(string, from_chars, to_chars)

stringcharacter-by-character

Replaces each character in string found in from_chars with the corresponding character in to_chars.
Unlike REPLACE(), this works one character at a time, not on substrings.
If to_chars is shorter than from_chars, extra from-characters are deleted.

Mapping: TRANSLATE('hello', 'el', 'ip')

e→i

l→p

h,o→unchanged

Deletion: TRANSLATE('hello', 'el', 'i')

e→i

l→deleted

SELECT TRANSLATE('hello', 'el', 'ip');
-- 'hippo'  (h unchanged, e→i, l→p, l→p, o unchanged)

SELECT TRANSLATE('hello', 'el', 'i');
-- 'hio'   (e→i, both l's deleted)

-- Useful for stripping unwanted chars:
TRANSLATE('(555)123-4567', '()-', '') → '5551234567'

Repeat & Utility String Functions

REPEAT(text, n)

string

Repeats text exactly n times.

REPEAT('ab', 3)  → 'ababab'
REPEAT('*', 5)  → '*****'
REPEAT('hi', 0) → ''

SPLIT_PART(text, delim, n)

stringpg-specific

Splits text on delim and returns the nth field (1-based).

SPLIT_PART('a,b,c', ',', 2) → 'b'
SPLIT_PART('2024-01-15', '-', 1) → '2024'

FORMAT(fmt, ...)

stringpg-specific

sprintf-style string formatting. %s for strings, %I for identifiers, %L for literals.

FORMAT('Hello %s!', 'World') → 'Hello World!'
FORMAT('%s is %s', 'SQL', 'fun') → 'SQL is fun'

Type Casting

CAST(col AS datatype) & ::

type

Converts a value to another data type. The :: operator is PostgreSQL shorthand for CAST().

SELECT CAST('42' AS INTEGER);    -- 42
SELECT '42'::INTEGER;             -- 42 (shorthand)
SELECT '3.14'::NUMERIC;           -- 3.14
SELECT 42::TEXT;                  -- '42'
SELECT '2024-01-15'::DATE;        -- date value

NULLIF(val, compare)

typediv-by-zero guard

Returns NULL if val equals compare; otherwise returns val. Essential for guarding against division by zero.

-- Guard against division by zero:
SELECT total / NULLIF(divisor, 0) FROM t;
-- Returns NULL instead of ERROR when divisor = 0

NULLIF('', '')   → NULL  (treat empty as NULL)
NULLIF(5, 5)     → NULL
NULLIF(5, 0)     → 5

Alt: add WHERE divisor != 0 to filter out zero rows entirely.

Numeric Functions

ROUND(n [, decimals])

numeric

Rounds to the nearest integer, or to decimals decimal places if specified.

ROUND(3.456)     → 3
ROUND(3.456, 2) → 3.46
ROUND(3.5)      → 4
ROUND(-3.5)     → -4

CEIL(n) / CEILING(n)

numeric

Returns the smallest integer greater than or equal to n (rounds up).

CEIL(4.1)   → 5
CEIL(4.0)   → 4
CEIL(-4.7)  → -4  (towards zero)

FLOOR(n)

numeric

Returns the largest integer less than or equal to n (rounds down).

FLOOR(4.9)   → 4
FLOOR(4.0)   → 4
FLOOR(-4.1)  → -5  (away from zero)

TRUNC(n [, decimals])

numericno rounding

Truncates toward zero — clips the decimal without rounding. Opposite of ROUND.

TRUNC(3.9)      → 3   (NOT 4)
TRUNC(-3.9)     → -3  (towards zero)
TRUNC(3.456, 2) → 3.45 (clip, not round)

Key difference: TRUNC(3.9) = 3 but ROUND(3.9) = 4

ABS(n)

numeric

Returns the absolute (non-negative) value of n.

ABS(-42)   → 42
ABS(42)    → 42
ABS(-3.14) → 3.14

MOD / Power / Sqrt

numeric

Other common math functions.

MOD(10, 3)      → 1   (remainder)
10 % 3           → 1   (same as MOD)
POWER(2, 8)     → 256
SQRT(144)       → 12
SIGN(-7)        → -1  (-1, 0, or 1)

Date & Time Functions

DATE_PART('part', source)

date/timepg-specific

Extracts a numeric field from a date/time value. PostgreSQL-specific function.

DATE_PART('year',   '2024-07-15'::DATE) → 2024
DATE_PART('month',  '2024-07-15'::DATE) → 7
DATE_PART('day',    '2024-07-15'::DATE) → 15
DATE_PART('hour',   NOW())               → current hour
DATE_PART('dow',    NOW())               → 0=Sun…6=Sat
DATE_PART('epoch', NOW())               → Unix timestamp

Parts: year, month, day, hour, minute, second, milliseconds, microseconds, dow (day of week), doy (day of year), week, quarter, epoch, timezone

EXTRACT(part FROM source)

date/time

SQL-standard equivalent of DATE_PART(). Returns the same values with slightly different syntax. Prefer this for portability.

EXTRACT(YEAR  FROM hire_date)
EXTRACT(MONTH FROM NOW())
EXTRACT(DOW   FROM created_at)

-- Practical example:
SELECT * FROM orders
WHERE EXTRACT(YEAR FROM order_date) = 2024;

Current Date & Time

date/time

Built-in functions and keywords for retrieving the current date and time.

NOW()              → timestamp with timezone
CURRENT_TIMESTAMP  → same as NOW()
CURRENT_DATE       → today's date only
CURRENT_TIME       → current time only
CLOCK_TIMESTAMP()  → real time mid-transaction

Date Arithmetic & Formatting

date/time

Add/subtract intervals; format dates as strings.

CURRENT_DATE + INTERVAL '7 days'
NOW() - INTERVAL '1 month'
AGE(end_date, start_date)   → interval
DATE_TRUNC('month', NOW()) → first of month
TO_CHAR(NOW(), 'YYYY-MM-DD') → '2024-07-15'
TO_DATE('15-07-2024', 'DD-MM-YYYY')

Quick Reference — All Functions at a Glance

||

concat (NULL-sensitive)

CONCAT()

concat (NULL-safe)

CONCAT_WS()

concat with separator

LENGTH()

string length

LOWER()

to lowercase

UPPER()

to uppercase

INITCAP()

title case

LTRIM()

trim left

RTRIM()

trim right

TRIM()

trim both ends

LPAD() RPAD()

pad string

SUBSTRING()

extract substring

LEFT() RIGHT()

first/last n chars

POSITION()

find position

REVERSE()

reverse string

REPEAT()

repeat string

REPLACE()

replace substring

TRANSLATE()

char-by-char replace

SPLIT_PART()

split by delimiter

FORMAT()

sprintf-style format

CAST() ::

type conversion

NULLIF()

null guard

ROUND()

round number

CEIL()

round up

FLOOR()

round down

TRUNC()

truncate (no round)

ABS()

absolute value

MOD() %

modulo remainder

DATE_PART()

extract date field (PG)

EXTRACT()

extract date field (SQL)

DATE_TRUNC()

truncate to precision

TO_CHAR()

date → string

TO_DATE()

string → date

NOW()

current timestamp

AGE()

interval between dates

INTERVAL

date arithmetic