PostgreSQL 18 reference · 6 entries
PostgreSQL Conditional and Type Expressions
Handle NULLs, conditions and types in PostgreSQL with COALESCE, NULLIF, GREATEST, LEAST, CASE, CAST, :: and pg_typeof().
NULL means "unknown": comparisons with it give NULL, so test with IS NULL / IS DISTINCT FROM and
replace it with COALESCE(). PostgreSQL is strict about types, so cast with :: when needed.
| Name | Description | Since |
|---|---|---|
COALESCE() |
Return the first argument that isn't NULL. The standard way to replace NULL with a default. | |
NULLIF() |
Return NULL if two values are equal, otherwise the first one. Commonly used to avoid division by zero. | |
GREATEST() |
Return the largest of a list of values, ignoring NULLs. least() returns the smallest. | |
CASE |
Choose a value from conditions: CASE WHEN cond THEN result … ELSE default END, usable anywhere an expression is. | |
CAST and :: |
Convert a value to another type with CAST(x AS type) or the PostgreSQL shorthand x::type. Invalid input raises an error. | |
PG_TYPEOF() |
Return the data type of a value as a name, such as integer, numeric or text. Useful for debugging casts. |