Functions & operators

The operator, aggregate, and set-returning catalog, generated at build time from spec/functions/catalog.toml. null describes NULL behavior (for example, kleene three-valued logic, or strict = NULL in, NULL out).

Operators

Name	Kind	Argument types	Result	NULL
or	logical	boolean, boolean	boolean	kleene
and	logical	boolean, boolean	boolean	kleene
not	logical	boolean	boolean	propagates
eq	comparison	integer, integer	boolean	propagates
ne	comparison	integer, integer	boolean	propagates
lt	comparison	integer, integer	boolean	propagates
gt	comparison	integer, integer	boolean	propagates
le	comparison	integer, integer	boolean	propagates
ge	comparison	integer, integer	boolean	propagates
is_distinct_from	comparison	integer, integer	boolean	null_safe
is_not_distinct_from	comparison	integer, integer	boolean	null_safe
eq	comparison	text, text	boolean	propagates
ne	comparison	text, text	boolean	propagates
lt	comparison	text, text	boolean	propagates
gt	comparison	text, text	boolean	propagates
le	comparison	text, text	boolean	propagates
ge	comparison	text, text	boolean	propagates
is_distinct_from	comparison	text, text	boolean	null_safe
is_not_distinct_from	comparison	text, text	boolean	null_safe
like	comparison	text, text	boolean	propagates
ilike	comparison	text, text	boolean	propagates
regex_match	comparison	text, text	boolean	propagates
regex_imatch	comparison	text, text	boolean	propagates
eq	comparison	boolean, boolean	boolean	propagates
ne	comparison	boolean, boolean	boolean	propagates
lt	comparison	boolean, boolean	boolean	propagates
gt	comparison	boolean, boolean	boolean	propagates
le	comparison	boolean, boolean	boolean	propagates
ge	comparison	boolean, boolean	boolean	propagates
is_distinct_from	comparison	boolean, boolean	boolean	null_safe
is_not_distinct_from	comparison	boolean, boolean	boolean	null_safe
eq	comparison	decimal, decimal	boolean	propagates
ne	comparison	decimal, decimal	boolean	propagates
lt	comparison	decimal, decimal	boolean	propagates
gt	comparison	decimal, decimal	boolean	propagates
le	comparison	decimal, decimal	boolean	propagates
ge	comparison	decimal, decimal	boolean	propagates
is_distinct_from	comparison	decimal, decimal	boolean	null_safe
is_not_distinct_from	comparison	decimal, decimal	boolean	null_safe
eq	comparison	bytea, bytea	boolean	propagates
ne	comparison	bytea, bytea	boolean	propagates
lt	comparison	bytea, bytea	boolean	propagates
gt	comparison	bytea, bytea	boolean	propagates
le	comparison	bytea, bytea	boolean	propagates
ge	comparison	bytea, bytea	boolean	propagates
is_distinct_from	comparison	bytea, bytea	boolean	null_safe
is_not_distinct_from	comparison	bytea, bytea	boolean	null_safe
eq	comparison	uuid, uuid	boolean	propagates
ne	comparison	uuid, uuid	boolean	propagates
lt	comparison	uuid, uuid	boolean	propagates
gt	comparison	uuid, uuid	boolean	propagates
le	comparison	uuid, uuid	boolean	propagates
ge	comparison	uuid, uuid	boolean	propagates
is_distinct_from	comparison	uuid, uuid	boolean	null_safe
is_not_distinct_from	comparison	uuid, uuid	boolean	null_safe
eq	comparison	timestamp, timestamp	boolean	propagates
ne	comparison	timestamp, timestamp	boolean	propagates
lt	comparison	timestamp, timestamp	boolean	propagates
gt	comparison	timestamp, timestamp	boolean	propagates
le	comparison	timestamp, timestamp	boolean	propagates
ge	comparison	timestamp, timestamp	boolean	propagates
is_distinct_from	comparison	timestamp, timestamp	boolean	null_safe
is_not_distinct_from	comparison	timestamp, timestamp	boolean	null_safe
eq	comparison	timestamptz, timestamptz	boolean	propagates
ne	comparison	timestamptz, timestamptz	boolean	propagates
lt	comparison	timestamptz, timestamptz	boolean	propagates
gt	comparison	timestamptz, timestamptz	boolean	propagates
le	comparison	timestamptz, timestamptz	boolean	propagates
ge	comparison	timestamptz, timestamptz	boolean	propagates
is_distinct_from	comparison	timestamptz, timestamptz	boolean	null_safe
is_not_distinct_from	comparison	timestamptz, timestamptz	boolean	null_safe
eq	comparison	interval, interval	boolean	propagates
ne	comparison	interval, interval	boolean	propagates
lt	comparison	interval, interval	boolean	propagates
gt	comparison	interval, interval	boolean	propagates
le	comparison	interval, interval	boolean	propagates
ge	comparison	interval, interval	boolean	propagates
is_distinct_from	comparison	interval, interval	boolean	null_safe
is_not_distinct_from	comparison	interval, interval	boolean	null_safe
eq	comparison	float, float	boolean	propagates
ne	comparison	float, float	boolean	propagates
lt	comparison	float, float	boolean	propagates
gt	comparison	float, float	boolean	propagates
le	comparison	float, float	boolean	propagates
ge	comparison	float, float	boolean	propagates
is_distinct_from	comparison	float, float	boolean	null_safe
is_not_distinct_from	comparison	float, float	boolean	null_safe
is_null	null_test	any	boolean	detects
is_not_null	null_test	any	boolean	detects
add	arithmetic	integer, integer	promoted	propagates
sub	arithmetic	integer, integer	promoted	propagates
mul	arithmetic	integer, integer	promoted	propagates
div	arithmetic	integer, integer	promoted	propagates
mod	arithmetic	integer, integer	promoted	propagates
neg	arithmetic	integer	promoted	propagates
add	arithmetic	decimal, decimal	promoted	propagates
sub	arithmetic	decimal, decimal	promoted	propagates
mul	arithmetic	decimal, decimal	promoted	propagates
div	arithmetic	decimal, decimal	promoted	propagates
mod	arithmetic	decimal, decimal	promoted	propagates
neg	arithmetic	decimal	promoted	propagates
add	arithmetic	interval, interval	interval	propagates
sub	arithmetic	interval, interval	interval	propagates
neg	arithmetic	interval	interval	propagates
add	arithmetic	timestamp, interval	timestamp	propagates
add	arithmetic	interval, timestamp	timestamp	propagates
sub	arithmetic	timestamp, interval	timestamp	propagates
add	arithmetic	timestamptz, interval	timestamptz	propagates
add	arithmetic	interval, timestamptz	timestamptz	propagates
sub	arithmetic	timestamptz, interval	timestamptz	propagates
sub	arithmetic	timestamp, timestamp	interval	propagates
sub	arithmetic	timestamptz, timestamptz	interval	propagates
mul	arithmetic	interval, integer	interval	propagates
mul	arithmetic	integer, interval	interval	propagates
mul	arithmetic	interval, decimal	interval	propagates
mul	arithmetic	decimal, interval	interval	propagates
div	arithmetic	interval, integer	interval	propagates
div	arithmetic	interval, decimal	interval	propagates
add	arithmetic	date, integer	date	propagates
add	arithmetic	integer, date	date	propagates
sub	arithmetic	date, integer	date	propagates
sub	arithmetic	date, date	i32	propagates
add	arithmetic	date, interval	timestamp	propagates
add	arithmetic	interval, date	timestamp	propagates
sub	arithmetic	date, interval	timestamp	propagates
add	arithmetic	float, float	promoted	propagates
sub	arithmetic	float, float	promoted	propagates
mul	arithmetic	float, float	promoted	propagates
div	arithmetic	float, float	promoted	propagates
mod	arithmetic	float, float	promoted	propagates
neg	arithmetic	float	promoted	propagates
abs	function	integer	promoted	propagates
abs	function	decimal	promoted	propagates
round	function	decimal	decimal	propagates
round	function	decimal, integer	decimal	propagates
round	function	integer	decimal	propagates
round	function	integer, integer	decimal	propagates
ceil	function	decimal	decimal	propagates
ceil	function	integer	decimal	propagates
ceiling	function	decimal	decimal	propagates
ceiling	function	integer	decimal	propagates
floor	function	decimal	decimal	propagates
floor	function	integer	decimal	propagates
trunc	function	decimal	decimal	propagates
trunc	function	integer	decimal	propagates
trunc	function	decimal, integer	decimal	propagates
trunc	function	integer, integer	decimal	propagates
abs	function	float	promoted	propagates
ceil	function	float	f64	propagates
ceiling	function	float	f64	propagates
floor	function	float	f64	propagates
trunc	function	float	f64	propagates
round	function	float	f64	propagates
round	function	float, integer	f64	propagates
sqrt	function	float	f64	propagates
exp	function	float	f64	propagates
ln	function	float	f64	propagates
log10	function	float	f64	propagates
pow	function	float, float	f64	propagates
power	function	float, float	f64	propagates
sqrt	function	decimal	decimal	propagates
exp	function	decimal	decimal	propagates
ln	function	decimal	decimal	propagates
log	function	decimal	decimal	propagates
log10	function	decimal	decimal	propagates
log	function	decimal, decimal	decimal	propagates
power	function	decimal, decimal	decimal	propagates
pow	function	decimal, decimal	decimal	propagates
sin	function	float	f64	propagates
cos	function	float	f64	propagates
tan	function	float	f64	propagates
cbrt	function	float	f64	propagates
pi	function	—	f64	propagates
radians	function	float	f64	propagates
degrees	function	float	f64	propagates
asin	function	float	f64	propagates
acos	function	float	f64	propagates
atan	function	float	f64	propagates
atan2	function	float, float	f64	propagates
cot	function	float	f64	propagates
sinh	function	float	f64	propagates
cosh	function	float	f64	propagates
tanh	function	float	f64	propagates
asinh	function	float	f64	propagates
acosh	function	float	f64	propagates
atanh	function	float	f64	propagates
sign	function	decimal	decimal	propagates
sign	function	float	f64	propagates
factorial	function	integer	decimal	propagates
scale	function	decimal	i32	propagates
min_scale	function	decimal	i32	propagates
trim_scale	function	decimal	decimal	propagates
make_interval	function	integer, integer, integer, integer, integer, integer, float	interval	propagates
make_timestamp	function	integer, integer, integer, integer, integer, float	timestamp	propagates
make_timestamptz	function	integer, integer, integer, integer, integer, float	timestamptz	propagates
make_timestamptz	function	integer, integer, integer, integer, integer, float, text	timestamptz	propagates
uuid_extract_version	function	uuid	i16	propagates
uuid_extract_timestamp	function	uuid	timestamptz	propagates
uuidv4	function	—	uuid	propagates
uuidv7	function	—	uuid	propagates
uuidv7	function	interval	uuid	propagates
now	function	—	timestamptz	propagates
clock_timestamp	function	—	timestamptz	propagates
nextval	function	text	i64	propagates
currval	function	text	i64	propagates
setval	function	text, integer	i64	propagates
setval	function	text, integer, boolean	i64	propagates
lastval	function	—	i64	propagates
current_setting	function	text	text	propagates
current_setting	function	text, boolean	text	propagates
num_nulls	function	any	i32	none
num_nonnulls	function	any	i32	none
array_ndims	function	anyarray	i32	propagates
array_length	function	anyarray, integer	i32	propagates
array_lower	function	anyarray, integer	i32	propagates
array_upper	function	anyarray, integer	i32	propagates
cardinality	function	anyarray	i32	propagates
array_dims	function	anyarray	text	propagates
array_to_json	function	anyarray	json	propagates
array_append	function	anyarray, anyelement	anyarray	none
array_prepend	function	anyelement, anyarray	anyarray	none
array_cat	function	anyarray, anyarray	anyarray	none
array_remove	function	anyarray, anyelement	anyarray	none
array_replace	function	anyarray, anyelement, anyelement	anyarray	none
array_position	function	anyarray, anyelement	i32	none
array_position	function	anyarray, anyelement, integer	i32	none
array_positions	function	anyarray, anyelement	i32[]	none
concat	concat	anyarray, anyarray	anyarray	none
concat	concat	anyarray, anyelement	anyarray	none
concat	concat	anyelement, anyarray	anyarray	none
contains	containment	anyarray, anyarray	boolean	propagates
contained_by	containment	anyarray, anyarray	boolean	propagates
overlaps	containment	anyarray, anyarray	boolean	propagates
contains	containment	anyrange, anyrange	boolean	propagates
contains	containment	anyrange, anyelement	boolean	propagates
contained_by	containment	anyrange, anyrange	boolean	propagates
contained_by	containment	anyelement, anyrange	boolean	propagates
overlaps	containment	anyrange, anyrange	boolean	propagates
strictly_left	containment	anyrange, anyrange	boolean	propagates
strictly_right	containment	anyrange, anyrange	boolean	propagates
not_extend_right	containment	anyrange, anyrange	boolean	propagates
not_extend_left	containment	anyrange, anyrange	boolean	propagates
adjacent	containment	anyrange, anyrange	boolean	propagates
lower	function	anyrange	anyelement	propagates
upper	function	anyrange	anyelement	propagates
lower	function	text	text	propagates
upper	function	text	text	propagates
length	function	text	i32	propagates
char_length	function	text	i32	propagates
character_length	function	text	i32	propagates
octet_length	function	text	i32	propagates
bit_length	function	text	i32	propagates
substr	function	text, integer	text	propagates
substr	function	text, integer, integer	text	propagates
left	function	text, integer	text	propagates
right	function	text, integer	text	propagates
lpad	function	text, integer	text	propagates
lpad	function	text, integer, text	text	propagates
rpad	function	text, integer	text	propagates
rpad	function	text, integer, text	text	propagates
btrim	function	text	text	propagates
btrim	function	text, text	text	propagates
ltrim	function	text	text	propagates
ltrim	function	text, text	text	propagates
rtrim	function	text	text	propagates
rtrim	function	text, text	text	propagates
replace	function	text, text, text	text	propagates
translate	function	text, text, text	text	propagates
repeat	function	text, integer	text	propagates
reverse	function	text	text	propagates
strpos	function	text, text	i32	propagates
split_part	function	text, text, integer	text	propagates
starts_with	function	text, text	boolean	propagates
ascii	function	text	i32	propagates
chr	function	integer	text	propagates
initcap	function	text	text	propagates
to_hex	function	integer	text	propagates
encode	function	bytea, text	text	propagates
decode	function	text, text	bytea	propagates
quote_literal	function	text	text	propagates
quote_ident	function	text	text	propagates
quote_nullable	function	text	text	none
regexp_replace	function	text, text, text	text	propagates
regexp_replace	function	text, text, text, text	text	propagates
regexp_match	function	text, text	text[]	propagates
regexp_match	function	text, text, text	text[]	propagates
regexp_like	function	text, text	boolean	propagates
regexp_like	function	text, text, text	boolean	propagates
regexp_count	function	text, text	i32	propagates
regexp_count	function	text, text, integer	i32	propagates
regexp_count	function	text, text, integer, text	i32	propagates
regexp_substr	function	text, text	text	propagates
regexp_substr	function	text, text, integer	text	propagates
regexp_substr	function	text, text, integer, integer	text	propagates
regexp_substr	function	text, text, integer, integer, text	text	propagates
regexp_substr	function	text, text, integer, integer, text, integer	text	propagates
regexp_instr	function	text, text	i32	propagates
regexp_instr	function	text, text, integer	i32	propagates
regexp_instr	function	text, text, integer, integer	i32	propagates
regexp_instr	function	text, text, integer, integer, integer	i32	propagates
regexp_instr	function	text, text, integer, integer, integer, text	i32	propagates
regexp_instr	function	text, text, integer, integer, integer, text, integer	i32	propagates
isempty	function	anyrange	boolean	propagates
lower_inc	function	anyrange	boolean	propagates
upper_inc	function	anyrange	boolean	propagates
lower_inf	function	anyrange	boolean	propagates
upper_inf	function	anyrange	boolean	propagates
i32range	function	integer, integer	i32range	none
i32range	function	integer, integer, text	i32range	none
i64range	function	integer, integer	i64range	none
i64range	function	integer, integer, text	i64range	none
numrange	function	decimal, decimal	numrange	none
numrange	function	decimal, decimal, text	numrange	none
tsrange	function	timestamp, timestamp	tsrange	none
tsrange	function	timestamp, timestamp, text	tsrange	none
tstzrange	function	timestamptz, timestamptz	tstzrange	none
tstzrange	function	timestamptz, timestamptz, text	tstzrange	none
daterange	function	date, date	daterange	none
daterange	function	date, date, text	daterange	none
range_union	arithmetic	anyrange, anyrange	anyrange	propagates
range_difference	arithmetic	anyrange, anyrange	anyrange	propagates
range_intersect	arithmetic	anyrange, anyrange	anyrange	propagates
range_merge	function	anyrange, anyrange	anyrange	propagates
jsonb_typeof	function	jsonb	text	propagates
json_typeof	function	json	text	propagates
jsonb_array_length	function	jsonb	i32	propagates
json_array_length	function	json	i32	propagates
jsonb_strip_nulls	function	jsonb	jsonb	propagates
json_strip_nulls	function	json	json	propagates
jsonb_pretty	function	jsonb	text	propagates
to_jsonb	function	any	jsonb	propagates
to_json	function	any	json	propagates
json_scalar	function	any	json	propagates
json_serialize	function	json	text	propagates
json_serialize	function	jsonb	text	propagates
jsonb_build_array	function	any	jsonb	none
json_build_array	function	any	json	none
jsonb_build_object	function	any	jsonb	none
json_build_object	function	any	json	none

Aggregates

Name	Argument types	Result
count_star	—	i64
count	any	i64
sum	integer	sum_widen
sum	decimal	decimal
avg	integer	decimal
avg	decimal	decimal
sum	float	same_as_input
avg	float	same_as_input
min	any	same_as_input
max	any	same_as_input
jsonb_agg	any	jsonb
json_agg	any	json
jsonb_agg_strict	any	jsonb
json_agg_strict	any	json

Set-returning functions

Called in FROM position as a computed row source — they expand their arguments into a set of rows. generate_series yields an integer series; unnest (polymorphic over anyarray) yields one row per array element. The produced relation has one column named after the function (or its alias).

Name	Argument types	Result column	NULL
generate_series	integer, integer	set_of_promoted	empty_on_null
generate_series	integer, integer, integer	set_of_promoted	empty_on_null
unnest	anyarray	set_of_element	empty_on_null
jsonb_array_elements	jsonb	jsonb	empty_on_null
jsonb_array_elements_text	jsonb	text	empty_on_null
jsonb_object_keys	jsonb	text	empty_on_null
json_object_keys	json	text	empty_on_null
jsonb_each	jsonb	jsonb	empty_on_null
jsonb_each_text	jsonb	text	empty_on_null