Supported PostgreSQL functions

This page defines the functions supported for PostgreSQL-dialect databases.

Mathematical functions

Unless otherwise specified, functions return the same data type as provided in the argument.

Function	Example / Notes	Description
`abs(float8 \| int8 \| numeric)`	`abs(-17) → 17`	Absolute value.
`acos(float8)`	`acos(1) → 0`	Inverse cosine, result in radians.
`asin(float8)`	`asin(1) → 1.5707963267948966`	Inverse sine, result in radians.
`atan(float8)`	`atan(1) → 0.7853981633974483`	Inverse tangent, result in radians.
`atan2(x float8, y float8)`	`atan2(1,0) → 1.5707963267948966`	Inverse tangent of `x/y`, result in radians.
`ceil(float8 \| numeric)`	`ceil(42.2::FLOAT8) → 43 ceil(-42.8::FLOAT8) → -42`	Nearest integer greater than or equal to argument.
`cos(float8)`	`cos(0) → 1`	Cosine, argument in radians.
`dexp(float8)`	`dexp(3) → 20.085536923187668`	Raise e to the specified exponent (e^x).
`div(x numeric, y numeric)`	`div(9, 4) → 2`	Integer quotient of x/y (truncates towards zero).
`dlog10(float8)`		Returns the base 10 logarithm of the provided value.
`dlog1(float8)`		Returns the value's natural logarithm.
`dpow(float8, float8)`		Returns the value of the first number raised to the power of the second number.
`dsqrt(float8)`		Returns the argument's square root.
`exp(float8)`	`exp(1.0::FLOAT8) → 2.7182818284590452`	Exponential (e raised to the given power).
`floor(float8 \| numeric)`	`floor(42.8::FLOAT8) → 42 floor(-42.8) → -43`	Nearest integer less than or equal to argument.
`ln(float8)`	`ln(2.0::FLOAT8) → 0.6931471805599453`	Natural logarithm.
`log(float8)`	`log(100.0::FLOAT8) → 2`	Base 10 logarithm.
`mod(x int8, y int8) mod(x numeric, y numeric)`	`mod(9,4) → 1`	Remainder of `x/y`.
`power(float8, float8)`	`power(9.0::FLOAT8, 3.0::FLOAT8) → 729`	`a` raised to the power of `b`. `pow` is an alias of `power`.
`round(float8)`	`round(42.4::FLOAT8) → 42`	Rounds to nearest integer.
`sign(float8)`	`sign(-8.4::FLOAT8) → -1`	Sign of the argument (-1, 0, or +1).
`sin(float8)`	`sin(1) → 0.8414709848078965`	Sine, argument in radians.
`sqrt(float8)`	`sqrt(2::FLOAT8) → 1.4142135623730951`	Square root.
`tan(float8)`	`tan(1) → 1.5574077246549023`	Tangent, argument in radians.
`trunc(float8)`	`trunc(42.8::FLOAT8) → 42 trunc(-42.8::FLOAT8) → -42`	Truncates to integer (towards zero).
`trunc(x numeric, y integer)`	`trunc(42.4382, 2) → 42.43`	Truncates x to y decimal places.

Array functions

Function	Example / Notes	Description
`array_cat(anyarray, anyarray)`	`array_cat(ARRAY['cat', 'dog'], ARRAY['bird', 'turtle']) → {"cat", "dog", "bird", "turtle"}`	Concatenates two arrays.
`array_to_string (array text_array, delimiter text [, null_string text ] )`	`array_to_string(ARRAY['a', NULL, 'c', 'd', 'e'], ',', '') → 'a',,'c','d','e' array_to_string(ARRAY['a', NULL, 'c', 'd', 'e'], ',') → 'a','c','d','e' array_to_string(ARRAY['a', NULL, 'c', 'd', 'e'], ',', NULL) → NULL array_to_string(ARRAY['a', NULL, 'c', 'd', 'e'], NULL, '*') → NULL`	Converts the values of the elements in a text array to their string representations. The first argument is the array which must be a text array. The second argument is a user-specified delimiter. The third (optional) argument is a user-specified null_string that the function substitutes for NULL values. If you don't pass a null_string, and the function encounters a NULL value, the NULL value is not included in the results of the function. If you pass NULL for either the delimiter argument or the null_string argument, then the entire array_to_string function returns `NULL`.

For details about the array aggregate function, see aggregate functions.

String functions

Function	Example / Notes	Description
`btrim(text)`	`btrim(' xyxyyx ') → xyxyyx`	Removes leading and trailing whitespace from the given string.
`btrim(string text, characters text)`	`btrim('xyxtrimyyx', 'xyz') → trim`	Removes the longest string containing only characters in characters from the start and end of string.
`concat(text, text[, text][, text])`	`concat('abcde'::text, 2::text, 22::text) → abcde222`	Concatenates the text representations of all the arguments. Note that all literals must be cast to text. `textcat` also concatenates text and does not require explicit casting of literals.
`length(text)`	Returns int8. `length('mike') → 4`	Returns the number of characters in the string.
`lower(text)`	`lower('PostgreSQL') → postgresql`	Converts the string to all lower case.
`lpad(text, int8)`	`lpad('hi', 7) → ␣␣␣␣␣hi`	Extends the string to the specified length by prepending spaces. If the string is already longer than length then it is truncated on the right.
`lpad(string text, length int8, fill text)`	`lpad('hi', 7, 'xy') → xyxyxhi`	Extends the string to length length by prepending the characters fill, repeated. If the string is already longer than length then it is truncated on the right.
`ltrim(text)`	`ltrim(' test') → test`	Removes leading spaces from a string.
`ltrim(string text, characters text)`	`ltrim('zzzytest', 'xyz') → test`	Removes the longest string containing only characters in characters from the start of string.
`regexp_replace(string text, pattern text, replacement text)`	`regexp_replace('Thomas', '.[mN]a.', 'M') → ThM`	Replaces substrings resulting from the first match of a POSIX regular expression. See the open-source PostgreSQL POSIX Regular Expressions documentation for more details.
`repeat(text, int8)`	Returns text. `repeat('Pg', 4) → PgPgPgPg`	Repeats a string the specified number of times.
`replace(string text, from text, to text)`	`replace('abcdefabcdef', 'cd', 'XX') → abXXefabXXef`	Replaces all occurrences in string of substring from with substring to.
`reverse(text)`	`reverse('abcde') → edcba`	Reverses the order of the characters in the string.
`rpad(text, int8)`	Returns text. In the following example, the result includes three trailing spaces. `rpad('hi', 5) → hi␣␣␣`	Extends the string to the specified length by appending spaces. If the string is already longer than the specified length then it is truncated.
`rpad(string text, length int8, fill text)`	Returns text. `rpad('hi', 5, 'xy') → hixyx`	Extends the string to length length by appending the characters fill, repeated if necessary. If the string is already longer than length then it is truncated.
`rtrim(text)`	`rtrim('test ') → test`	Removes trailing spaces from a string.
`rtrim(string text, characters text)`	`rtrim('testxxzx', 'xyz') → test`	Removes the longest string containing only characters in characters from the end of string.
`starts_with(string text, prefix text)`	Returns Boolean. `starts_with('alphabet', 'alph') → true`	Returns true if string starts with prefix.
`strpos(string text, substring text)`	Returns int8. `strpos('high', 'ig') → 2`	Returns first starting index of the specified substring within string, or zero if it's not present.
`substr(text, int8)`	`substr('alphabet', 3) → phabet`	Extracts the substring of the provided text starting at the specified character.
`substr(string text, start int8, count int8)`	Returns text. `substr('alphabet', 3, 2) → ph`	Extracts the substring of string starting at the start character, and extending for count characters.
`textconcat(text, text[, text][, text])`	`concat('abcde', 2, NULL, 22) → abcde222`	Concatenates the text representations of all the arguments. NULL arguments are ignored.
`upper(text)`	`upper('hello') → HELLO`	Converts the string to all upper case.

Binary string functions

Function	Example / Notes	Description
`btrim(bytes bytea, bytesremoved bytea)`	`btrim('\x1234567890'::bytea, '\x9012'::bytea) → \x345678`	Removes the longest string containing only bytes appearing in bytesremoved from the start and end of bytes.
`length(bytea)`	Returns int8. `length('\x1234567890'::bytea) → 5`	Returns the number of bytes in the binary string.
`sha256(bytea)`	`sha256('abc'::bytea) → ungWv48Bz+pBQUDeXa4iI7ADYaOWF3qctBD/YfIAFa0=`	Computes the SHA-256 hash of the binary string.
`sha512(bytea)`	`sha512('abc'::bytea) → 3a81oZNherrMQXNJriBBMRLm+k6JqX6iCp7u5ktV05ohkpkqJ0/BqDa6PCOj/uu9RU1EI2Q86A4qmslPpUyknw==`	Computes the SHA-512 hash of the binary string.
`substr(bytes bytea, start int8)`	`substr('\x1234567890'::bytea, 3) → \x567890`	Extracts the substring of bytes starting at the start byte.
`substr(bytes bytea, start int8, count int8)`	`substr('\x1234567890'::bytea, 3, 2) → \x5678`	Extracts the substring of bytes starting at the start byte, and extending for count bytes.

Date/time functions

Function	Example / Notes	Description
`current_date`	`SELECT CURRENT_DATE; Result: 2022-05-13`	Current date
`current_timestamp`	`SELECT CURRENT_TIMESTAMP; Result: 2022-05-13T16:30:29.880850967Z`	Current date and time.
`make_date(int8, int8, int8)`	`make_date(2013, 7, 15) → 2013-07-15`	Create date from year, month and day fields (negative years signify BC)
`now()`	Returns timestamptz. `now() → 2022-05-02T19:17:45.145511221Z`	Current date and time.
`to_timestamp(int8)`	Returns timestamptz. `to_timestamp(1284352323) → 2010-09-13T04:32:03Z`	Convert Unix epoch (seconds since 1970-01-01 00:00:00+00) to timestamp with time zone.

JSONB functions and operators

This topic presents information about the JSONB functions and operators as they're used in Spanner.

For more information, see the PostgreSQL JSONB documentation.

JSONB functions

Function Example / Notes Description

Function	Example / Notes	Description
`to_jsonb(ANY)`	`to_jsonb(1.2334000) → 1.2334000` `to_jsonb(true) → true` `to_jsonb('abc'::varchar) → abc`	Converts the given value to `jsonb`.
`jsonb_typeof(jsonb)`	`jsonb_typeof('-123.4') → number` `jsonb_typeof('{"a":1, "b":2}') → object` `jsonb_typeof('["a", "b", "c"]') → array` `jsonb_typeof('null'::jsonb) → null` `jsonb_typeof(NULL) IS NULL → true`	Returns the type of the top-level `jsonb` value as a text string. The possible types are `object`, `array`, `string`, `number`, `boolean`, and `null`. The `null` result should not be confused with a SQL NULL, as the examples illustrate.

to_jsonb(ANY)

to_jsonb(1.2334000) → 1.2334000

to_jsonb(true) → true

to_jsonb('abc'::varchar) → abc

Converts the given value to jsonb.

jsonb_typeof(jsonb)

jsonb_typeof('-123.4') → number

jsonb_typeof('{"a":1, "b":2}') → object

jsonb_typeof('["a", "b", "c"]') → array

jsonb_typeof('null'::jsonb) → null

jsonb_typeof(NULL) IS NULL → true

Returns the type of the top-level jsonb value as a text string. The possible types are object, array, string, number, boolean, and null.

The null result should not be confused with a SQL NULL, as the examples illustrate.

JSONB operators

Operator Example / Notes Description

Operator	Example / Notes	Description
`->`	`'{"a": {"b":"foo"}}'::jsonb -> 'a' → {"b": "foo"}` `'[{"a":"foo"},{"b":"bar"},{"c":"baz"}]'::jsonb -> 2 → {"c": "baz"}` `'{"a": {"b":"foo"}}'::jsonb -> 'a' -> 'b' → foo` `'[{"a":"foo"},{"b":"bar"},{"c":"baz"}]'::jsonb -> -1 IS NULL → true`	Takes text or an integer as an argument and returns a `jsonb` object. When the argument is text, a `jsonb` object field is extracted with the given key. When the argument is an integer n, the nth element of a `jsonb` array is returned. The operator can be chained to extract nested values. See the third example provided. Negative indexes are not supported. If they're used, SQL NULL is returned. See the last example provided.
`->>`	`'{"a": {"b":"foo"}}'::jsonb ->> 'a' → {"b": "foo"}` `'[{"a":"foo"},{"b":"bar"},{"c":"baz"}]'::jsonb ->> 2 → {"c": "baz"}` `'[{"a":"foo"},{"b":"bar"},{"c":"baz"}]'::jsonb ->> -1 IS NULL → true`	Takes text or an integer as an argument and returns text. When the argument is text, a `jsonb` object field is extracted with the given key. When the argument is an integer n, the nth element of a `jsonb` array is returned. Negative indexes are not supported. If they're used, SQL NULL is returned. See the last example provided.

->

'{"a": {"b":"foo"}}'::jsonb -> 'a' → {"b": "foo"}

'[{"a":"foo"},{"b":"bar"},{"c":"baz"}]'::jsonb -> 2 → {"c": "baz"}

'{"a": {"b":"foo"}}'::jsonb -> 'a' -> 'b' → foo

'[{"a":"foo"},{"b":"bar"},{"c":"baz"}]'::jsonb -> -1 IS NULL → true

Takes text or an integer as an argument and returns a jsonb object.

When the argument is text, a jsonb object field is extracted with the given key.

When the argument is an integer n, the nth element of a jsonb array is returned.

The operator can be chained to extract nested values. See the third example provided.

Negative indexes are not supported. If they're used, SQL NULL is returned. See the last example provided.

->>

'{"a": {"b":"foo"}}'::jsonb ->> 'a' → {"b": "foo"}

'[{"a":"foo"},{"b":"bar"},{"c":"baz"}]'::jsonb ->> 2 → {"c": "baz"}

'[{"a":"foo"},{"b":"bar"},{"c":"baz"}]'::jsonb ->> -1 IS NULL → true

Takes text or an integer as an argument and returns text.

When the argument is text, a jsonb object field is extracted with the given key.

When the argument is an integer n, the nth element of a jsonb array is returned.

Negative indexes are not supported. If they're used, SQL NULL is returned. See the last example provided.

Aggregate functions

Function	Example / Notes	Description
`array_agg(anynonarray)`		Inserts the given values, including nulls, into an array.
`avg(float8 \| int8 \| numeric)`		Computes the average (arithmetic mean) of all the non-null input values.
`bit_and(int8)`		Computes the bitwise AND of all non-null input values.
`bit_or(int8)`		Computes the bitwise OR of all non-null input values.
`bool_and(bool)`		Returns true if all non-null input values are true, otherwise false.
`bool_or(bool)`		Returns true if any non-null input value is true, otherwise false.
`count()`	Returns int8.	Computes the number of input rows.
`count(bool \| bytea \| float8 \| int8 \| text \| timestamptz)`	Returns int8.	Computes the number of input rows in which the input value is not null.
`every(bool)`		Equivalent to `bool_and()`.
`max(float8 \| int8 \| numeric \| text \| timestamptz)`	Returns same type as input type.	Computes the maximum of the non-null input values.
`min(float8 \| int8 \| numeric \| text \| timestamptz)`		Computes the minimum of the non-null input values.
`string_agg(value bytea, delimiter bytea)`		Concatenates the non-null input values into a string. Each value after the first is preceded by the corresponding delimiter (if it's not null).
`string_agg(value text, delimiter text)`		Concatenates the non-null input values into a string. Each value after the first is preceded by the corresponding delimiter (if it's not null).
`sum(float8 \| int8 \| numeric)`		Computes the sum of the non-null input values.

Conditional functions

Function	Example / Notes	Description
`coalesce(ANY REPEATED)`	`coalesce(NULL, 'abc', 'def') → 'abc'`	Returns the first of its arguments that is not null. Null is returned only if all arguments are null. It is often used to substitute a default value for null values when data is retrieved for display. The arguments must all be of the same data type, which will be the type of the result.
`greatest(ANY REPEATED)`	`greatest(6, 10, 3, 14, 2) → 14`	Returns the largest value from a list of any number of expressions. The expressions must all be of the same data type, which will be the type of the result. NULL values in the list are ignored. The result will be NULL only if all the expressions evaluate to NULL.
`least(ANY REPEATED)`	`least(6, 10, 3, 14, 2) → 2`	Returns the smallest value from a list of any number of expressions. The expressions must all be of the same data type, which will be the type of the result. NULL values in the list are ignored. The result will be NULL only if all the expressions evaluate to NULL.
`nullif(value1 ANY, value2 ANY)`		Returns a null value if value1 equals value2; otherwise it returns value1. The two arguments must be of comparable types. To be specific, they are compared exactly as if you had written value1 = value2, so there must be a suitable = operator available. The result has the same type as the first argument — but there is a subtlety. What is actually returned is the first argument of the implied = operator, and in some cases that will have been promoted to match the second argument's type. For example, NULLIF(1, 2.2) yields numeric, because there is no integer = numeric operator, only numeric = numeric.
`ISNULL`	`datatype ISNULL → boolean`	Tests whether value is null (nonstandard syntax).
`NOTNULL`	`datatype NOTNULL → boolean`	Tests whether value is not null (nonstandard syntax).
`IS TRUE`	`boolean IS TRUE → boolean true IS TRUE → true NULL::boolean IS TRUE → false (rather than NULL)`	Tests whether boolean expression yields true.
`IS NOT TRUE`	`boolean IS NOT TRUE → boolean true IS NOT TRUE → false NULL::boolean IS NOT TRUE → true (rather than NULL)`	Tests whether boolean expression yields false or unknown.
`IS FALSE`	`boolean IS FALSE → boolean true IS FALSE → false NULL::boolean IS FALSE → false (rather than NULL)`	Tests whether boolean expression yields false.
`IS NOT FALSE`	`boolean IS NOT FALSE → boolean true IS NOT FALSE → true NULL::boolean IS NOT FALSE → true (rather than NULL)`	Tests whether boolean expression yields true or unknown.
`IS UNKNOWN`	`boolean IS UNKNOWN → boolean true IS UNKNOWN → false NULL::boolean IS UNKNOWN → true (rather than NULL)`	Tests whether boolean expression yields unknown.
`IS NOT UNKNOWN`	`boolean IS NOT UNKNOWN → boolean true IS NOT UNKNOWN → true NULL::boolean IS NOT UNKNOWN → false (rather than NULL)`	Tests whether boolean expression yields true or false.

Pattern matching functions

Function	Example / Notes	Description
`like(string bytea, pattern bytea)`	Returns Boolean.	Returns true if the string matches the supplied pattern. More information about LIKE is available from the postgresql.org documentation.
`like(string text, pattern text)`	Returns Boolean.	Returns true if the string matches the supplied pattern. More information about LIKE is available from the postgresql.org documentation.
`substring(string bytea, start int8, count int8)`	`substring('\x1234567890'::bytea, 3, 2) → \x5678`	Returns a substring of string, starting at the start character and continuing for count characters.
`substring(string bytea, start int8)`	`substring('\x1234567890'::bytea, 3) → \x567890`	Returns a substring of string, from the start character to the end of the string.
`substring(string text, start int8, count int8)`	`substring('documentation', 2, 2) → oc`	Returns a substring of string, starting at the start character and continuing for count characters.
`substring(string text, start int8)`	`substring('documentation', 2) → ocumentation`	Returns a substring of string, from the start character to the end of the string.