Emergency Reference

PostgreSQL Cheatsheet

The 50 functions you'll reach for most — compact syntax reminders designed for bookmarking, printing, and 2am emergencies.

🔤

String

lower/upper→ textChange case

lower(str) / upper(str)

lower('Hello')→hello

length→ intCharacter count

length(str)

length('café')→4

trim→ textStrip leading/trailing chars

trim([BOTH|LEADING|TRAILING] [chars] FROM str)

trim(' hi ')→hi

substring→ textExtract by position or regex

substring(str FROM start [FOR len])

substring('hello' FROM 2 FOR 3)→ell

replace→ textLiteral string replacement (all occurrences)

replace(str, from, to)

replace('aXbX','X','!')→a!b!

regexp_replace→ textRegex replacement; 'g' flag for all matches

regexp_replace(str, pat, repl [, flags])

regexp_replace('a1b2','[0-9]','N','g')→aNbN

split_part→ textReturn Nth field after splitting; safe out-of-bounds

split_part(str, delimiter, n)

split_part('a,b,c',',',2)→b

concat_ws→ textJoin with separator, skipping NULLs

concat_ws(sep, val1, val2, ...)

concat_ws(' ','John',NULL,'Doe')→John Doe

format→ text%s value, %I identifier, %L literal (injection-safe)

format(fmt, arg1, ...)

format('%I = %L','col','val')→col = 'val'

lpad / rpad→ textPad to fixed width

lpad(str, len [, fill])

lpad('42',6,'0')→000042

strpos→ intPosition of substring; 0 if not found

strpos(str, substr)

strpos('hello','ll')→3

left / right→ textFirst/last N chars; negative skips from other end

left(str, n) / right(str, n)

right('abcdef',-2)→cdef

initcap→ textTitle-case each word

initcap(str)

initcap('hello world')→Hello World

translate→ textCharacter-by-character substitution (single pass)

translate(str, from, to)

translate('hello','aeiou','*****')→h*ll*

md5→ textMD5 hex digest (not for passwords)

md5(str)

md5('hello')→5d41402a...

encode / decode→ text/byteaBase64 or hex encoding of binary data

encode(data, 'base64') / decode(str, 'base64')

encode('Hi'::bytea,'base64')→SGk=

regexp_match→ text[]Return capture groups of first regex match

regexp_match(str, pattern [, flags])

(regexp_match('v14.2','v(\d+)'))[1]→14

string_to_array→ text[]Split string into array

string_to_array(str, delimiter)

string_to_array('a,b,c',',')→{a,b,c}

starts_with→ boolTest string prefix

starts_with(str, prefix)

starts_with('hello','he')→true

reverse→ textReverse a string

reverse(str)

reverse('hello')→olleh

📅

Date & Time

now()→ timestamptzCurrent transaction start time (stable within transaction)

now()

SELECT now();→2024-03-15 14:30:00+00

current_date→ dateCurrent date in session timezone

current_date

SELECT current_date;→2024-03-15

date_trunc→ timestampTruncate to time unit precision

date_trunc(unit, timestamp)

date_trunc('month', now())→2024-03-01 00:00:00+00

date_part / extract→ float8/numericExtract a field from a timestamp

extract(field FROM timestamp)

extract(year FROM now())→2024

age()→ intervalDifference between two timestamps

age(timestamp, timestamp)

age('2024-01-01','2023-01-01')→1 year

interval arithmetic→ timestampAdd/subtract intervals

timestamp ± interval

now() + interval '7 days'→now + 7 days

to_timestamp→ timestamptzConvert epoch seconds to timestamp

to_timestamp(double precision)

to_timestamp(1700000000)→2023-11-14 22:13:20+00

to_char (date)→ textFormat date/time as string

to_char(timestamp, format)

to_char(now(),'YYYY-MM-DD')→2024-03-15

timezone()→ timestampConvert timestamp to specified timezone

timezone(zone, timestamp)

timezone('America/New_York', now())→Local NYC time

make_date→ dateBuild a date from year, month, day

make_date(year, month, day)

make_date(2024,12,31)→2024-12-31

🔢

Math

round→ numericRound to n decimal places

round(v numeric, n int)

round(3.14159, 2)→3.14

floor / ceil→ numericRound down / up to nearest integer

floor(v) / ceil(v)

floor(4.9)→4

abs→ sameAbsolute value

abs(v)

abs(-42)→42

mod→ sameRemainder of division

mod(a, b) or a % b

mod(10, 3)→1

power / sqrt→ float8Exponentiation / square root

power(base, exp) / sqrt(v)

power(2,10)→1024

random()→ float8Random value in [0.0, 1.0)

random()

floor(random()*100)::int→Random 0–99

trunc→ numericTruncate (toward zero), no rounding

trunc(v [, n])

trunc(3.99)→3

greatest / least→ sameMaximum / minimum of a list

greatest(a, b, ...) / least(a, b, ...)

greatest(1,5,3)→5

📦

JSON & JSONB

-> / ->>→ json/textExtract JSON field (-> keeps JSON, ->> returns text)

col->'key' or col->index

'{"a":1}'::jsonb -> 'a'→1

#> / #>>→ json/textExtract at path array

col #> '{key,subkey}'

data #>> '{user,name}'→John

jsonb_set→ jsonbReplace or insert a value at path

jsonb_set(doc, path, new_val [, create])

jsonb_set(doc, '{a}', '99')→doc with a=99

jsonb_build_object→ jsonbBuild JSONB object from key-value pairs

jsonb_build_object(k1, v1, k2, v2, ...)

jsonb_build_object('id',1,'name','x')→{"id":1,"name":"x"}

jsonb_agg→ jsonbAggregate rows into a JSON array

jsonb_agg(expression)

SELECT jsonb_agg(name) FROM users→["Alice","Bob"]

jsonb_each→ setof (key,value)Expand JSONB object into key-value rows

jsonb_each(jsonb)

SELECT * FROM jsonb_each('{"a":1}'::jsonb)→a | 1

jsonb_array_elements→ setof jsonbExpand JSON array into rows

jsonb_array_elements(jsonb)

SELECT * FROM jsonb_array_elements('[1,2,3]'::jsonb)→1,2,3 rows

row_to_json→ jsonConvert a row to JSON

row_to_json(record)

SELECT row_to_json(u) FROM users u LIMIT 1→{"id":1,"name":"..."}

∑

Aggregate

count→ bigintCount rows; count(*) includes NULLs

count(*) / count(col)

SELECT count(*) FROM users→1000

sum / avg→ numericSum or average of non-NULL values

sum(col) / avg(col)

avg(price)→24.99

min / max→ sameMinimum / maximum value

min(col) / max(col)

max(created_at)→Most recent timestamp

string_agg→ textConcatenate strings with separator

string_agg(col, delimiter ORDER BY ...)

string_agg(name,', ')→Alice, Bob, Carol

array_agg→ arrayCollect values into an array

array_agg(col ORDER BY ...)

array_agg(id ORDER BY id)→{1,2,3}

bool_and / bool_or→ booleanAll true / any true across rows

bool_and(condition)

bool_and(active)→false if any inactive

🪟

Window

row_number()→ bigintUnique sequential row number within partition

row_number() OVER (PARTITION BY ... ORDER BY ...)

row_number() OVER (ORDER BY salary DESC)→1, 2, 3...

rank() / dense_rank()→ bigintRank with/without gaps on ties

rank() OVER (...)

rank() OVER (ORDER BY score DESC)→1,2,2,4 vs 1,2,2,3

lag / lead→ sameValue from previous/next row in partition

lag(col, offset, default) OVER (...)

lag(price,1,0) OVER (ORDER BY date)→Previous row's price

first_value / last_value→ sameFirst/last value in the window frame

first_value(col) OVER (...)

first_value(price) OVER (PARTITION BY category ORDER BY date)→Oldest price in category

ntile(n)→ intDivide rows into n equal buckets

ntile(n) OVER (ORDER BY ...)

ntile(4) OVER (ORDER BY score)→1–4 quartile assignment

sum() OVER→ numericRunning total within window

sum(col) OVER (ORDER BY ... ROWS UNBOUNDED PRECEDING)

sum(amount) OVER (ORDER BY date)→Cumulative sum

🔀

Conditional

COALESCE→ sameFirst non-NULL value in list

COALESCE(v1, v2, ...)

COALESCE(nickname, first_name, 'Anonymous')→First non-null name

NULLIF→ sameNULL if two values are equal

NULLIF(a, b)

NULLIF(count, 0)→NULL instead of zero

CASE WHEN→ anyConditional expression

CASE WHEN cond THEN val ... ELSE val END

CASE WHEN score>90 THEN 'A' ELSE 'B' END→Grade letter

GREATEST / LEAST→ sameMax/min of arbitrary value list (NULL-skipping)

GREATEST(a, b, ...) / LEAST(a, b, ...)

GREATEST(0, col - 10)→0 if col < 10

pgref.dev — PostgreSQL 16 — for complete docs with examples and pro tips, browse by category