Supported PostgreSQL functions

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

The content on this page is based on the PostgreSQL documentation, which is available under the PostgreSQL License. There are differences in behavior between the PostgreSQL capabilities supported in Spanner and their open-source PostgreSQL equivalents.

Mathematical functions

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

Function	Example/Notes	Description
abs(float8 | float4 | 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.
spanner.bit_reverse(bigint_value, /*preserve_sign=*/bool)	spanner.bit_reverse(1, true); --> returns 4611686018427387904 spanner.bit_reverse(10, false); --> returns 5764607523034234880	Returns a bit-reversed value for a bigint value. When preserve_sign is true, this function provides the same bit-reversal algorithm used in bit-reversed sequence. See Bit-reversed sequence.
spanner.cosine_distance(float4[], float4[]) spanner.cosine_distance(float8[], float8[])	Returns float8. spanner.cosine_distance('{1.0, 2.0}'::float4[], '{3.0, 4.0}'::float4[]) → 0.016130 spanner.cosine_distance('{2.0, 1.0}'::float8[], '{4.0, 3.0}'::float8[]) → 0.016130	Computes the cosine distance between two vectors. Each vector represents a quantity that includes magnitude and direction. Vectors are represented as float4[] or float8[]. A vector can have one or more dimensions. Both vectors in this function must share these same dimensions, and if they don't, an error is produced. The ordering of numeric values in a vector doesn't impact the results produced by this function. An error is produced if an element or field in a vector is null. A vector can't be a zero vector. A vector is a zero vector if all elements in the vector are 0. For example, '{0.0, 0.0}'::float4. If a zero vector is encountered, an error is produced. If either of the arguments is null, null is returned. Preview This feature is subject to the "Pre-GA Offerings Terms" in the General Service Terms section of the Service Specific Terms. Pre-GA features are available "as is" and might have limited support. For more information, see the launch stage descriptions.
spanner.dot_product(int8[], int8[]) spanner.dot_product(float4[], float4[]) spanner.dot_product(float8[], float8[])	Returns float8. spanner.dot_product('{100}'::int8[], '{200}'::int8[]) → 20000 spanner.dot_product('{100, 10}'::float4[], '{200, 6}'::float4[]) → 20060	Computes the dot product of two vectors. The dot product is computed by summing the product of corresponding vector elements. Each vector represents a quantity that includes magnitude and direction. Vectors are represented as int8[], float4[], or float8[]. A vector can have one or more dimensions. Both vectors in this function must share these same dimensions, and if they don't, an error is produced. The ordering of numeric values in a vector doesn't impact the results produced by this function. An error is produced if an element or field in a vector is null. A vector can be a zero vector. A vector is a zero vector if it has no dimensions or if all elements in the vector are 0. For example, '{0.0, 0.0}'::float4. If a zero vector is encountered, an error is produced. If either of the arguments is null, null is returned. Preview This feature is subject to the "Pre-GA Offerings Terms" in the General Service Terms section of the Service Specific Terms. Pre-GA features are available "as is" and might have limited support. For more information, see the launch stage descriptions.
spanner.euclidean_distance(float4[], float4[]) spanner.euclidean_distance(float8[], float8[])	Returns float8. spanner.euclidean_distance('{1.0, 2.0}'::float4[], '{3.0, 4.0}'::float4[]) → 2.828 spanner.euclidean_distance('{2.0, 1.0}'::float8[], '{4.0, 3.0}'::float8[]) → 2.828	Computes the Euclidean distance between two vectors. Each vector represents a quantity that includes magnitude and direction. Vectors are represented as float4[] or float8[]. A vector can have one or more dimensions. Both vectors in this function must share these same dimensions, and if they don't, an error is produced. The ordering of numeric values in a vector doesn't impact the results produced by this function. An error is produced if an element or field in a vector is null. A vector can be a zero vector. A vector is a zero vector if all elements in the vector are 0. For example, '{0.0, 0.0}'::float4. If either of the arguments is null, null is returned. Preview This feature is subject to the "Pre-GA Offerings Terms" in the General Service Terms section of the Service Specific Terms. Pre-GA features are available "as is" and might have limited support. For more information, see the launch stage descriptions.
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.

Machine learning functions

Function	Example / Notes	Description
spanner.ML_PREDICT_ROW(model_endpoint text|jsonb, args jsonb)	Returns JSONB.	spanner.ML_PREDICT_ROW is a scalar function that allows predictions on a per row basis and can appear anywhere a scalar expression is allowed in SQL statements. You can get online predictions in your SQL code by calling this function. For more information about this function, see Use ML Predict for ML serving.

Array functions

PostgreSQL interface note: The PostgreSQL interface does not support multi-dimensional arrays.

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.
array_upper(anyarray, dimension int8)	array_upper(ARRAY[1, 2, 3, 4], 1) → 4	Returns the upper bound of the requested array dimension. Note that Spanner does not support multidimensional arrays. The only dimension supported is 1. For more information, see Working with arrays in PostgreSQL-dialect databases.
array_length(anyarray, dimension int8)	array_length(ARRAY[1, 2, 3, 4], 1) → 4	Returns the size of the array. Returns NULL for an empty or NULL array, or if the dimension is NULL. multidimensional arrays are not supported. The only dimension supported is 1. For more information, see Working with arrays in PostgreSQL-dialect databases.
array(subquery)		Returns an ARRAY with one element for each row in the subquery. For more information, see Working with arrays in PostgreSQL-dialect databases.
unnest(anyarray)		Expands an ARRAY into a set of rows. For more information, see Working with arrays in PostgreSQL-dialect databases.

• 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[, ...])	concat('abcde', 2::text, '', 22::text) → abcde222 concat('abcde', 2::text, NULL, 22::text) → NULL	Concatenates the provided text arguments. Non-text arguments must first be explicitly cast to text. Any SQL NULL argument will result in a SQL NULL result. textcat also concatenates text.
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.
quote_ident(string text)	quote_ident('Example') → "Example"	Given a string argument, returns a quoted identifier suitable for inclusion in SQL statements.
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. For more information, see the open-source PostgreSQL POSIX Regular Expressions documentation.
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(string text, start int8)	substr('alphabet', 3) → phabet	Extracts the substring of the provided text starting at the specified character.
substr(string text, start int8, count int8)	substr('alphabet', 3, 2) → ph	Extracts the substring of string starting at the start character, and extending for count characters.
substring(string text,pattern text)	substring('exampletext', 'tex.') → 'text'	Extracts the substring that matches a POSIX regular expression. For more information, see the open-source PostgreSQL POSIX Regular Expressions documentation.
textcat(string text, string text)	textcat('abcde', '222') → abcde222 textcat('abcde', NULL) → NULL	Concatenates the text representations of the two arguments. Any SQL NULL argument results in a SQL NULL result.
upper(string 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 and operators

Date/time functions

Function	Example / Notes	Description
current_date	SELECT CURRENT_DATE; Result: 2022-05-13	Returns current date.
current_timestamp	SELECT CURRENT_TIMESTAMP; Result: 2022-05-13T16:30:29.880850967Z	Returns current date and time in timestamptz format.
date_trunc(text, timestamptz)	date_trunc('day', timestamptz '2020-01-02 13:14:15+0') -> 2020-01-02 00:00:00-08	Truncates a timestamp to the precision of the provided field. The truncation is done with respect to the default time zone (America/Los_Angeles)
date_trunc(text, timestamptz, text)	date_trunc('day', timestamptz '2001-02-16 20:38:40+00', 'Australia/Sydney') -> 2001-02-16 08:00:00-05	Truncates a timestamp to the precision of the provided field. The trunctation is done with respect to the provided time zone
extract(field FROM source)	extract(decade from timestamptz '2001-01-01 01:00:00+00') -> 200	Retrieves subfields from date/time values and returns values of type numeric. Source can be of type date or timestamptz.
make_date(int8, int8, int8)	make_date(2013, 7, 15) → 2013-07-15	Creates date from year, month, and day fields (negative years signify BCE).
now()	now() → 2022-05-02T19:17:45.145511221Z	Returns current date and time in timestamptz format.
to_timestamp(int8)	to_timestamp(1284352323) → 2010-09-13T04:32:03Z	Converts Unix epoch (seconds since 1970-01-01 00:00:00+00) to timestamptz format.

Spanner specific date/time functions

Spanner has several functions that perform date or time math that accept INTERVAL values in TEXT form. You must use the spanner namespace to call these functions.

Function	Example / Notes	Description
spanner.date_bin(text, timestamptz, timestamptz)	spanner.date_bin('15 minutes', timestamptz '2001-02-16 20:38:40Z', timestamptz '2001-02-16 20:05:00Z') -> 2001-02-16 20:35:00Z	Bins input into a specified interval aligned with a specified origin.
spanner.timestamptz_add(timestamptz, text)	spanner.timestamptz_add(timestamptz '2001-02-16 20:38:40Z', '1 day 3min') -> 2001-02-17 20:41:40Z	Adds an interval to a timestamptz.
spanner.timestamptz_subtract(timestamptz, text)	spanner.timestamptz_subtract(timestamptz '2001-02-16 20:38:40Z', '1 month 2 hours') -> 2001-01-16 18:38:40Z	Subtracts an interval from a timestamptz.

Date/time operators

Operator	Example / Notes	Description
date - date	date '2001-10-01' - date '2001-09-28' → 3	Subtracts dates, returning the number of days that have elapsed.
date - integer	date '2001-10-01' - 7 → 2001-09-24	Subtracts a number of days from a date, returning the new date.
date + integer	date '2001-09-28' + 7 → 2001-10-05	Adds a number of days to a date, returning the new date.

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
jsonb_build_array(ANY[, ...])	jsonb_build_array(1, 'abc') -> [1, "abc"]	Builds a jsonb array out of a variadic argument list.
jsonb_build_object(TEXT, ANY[, ...])	jsonb_build_object('key1', 1, 'key2', 'abc') -> {"key": 1, "key2": "abc"}	Builds a jsonb object out of a variadic argument list. The argument list consists of alternating keys and values. The keys are of type text.
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.

Spanner specific JSONB functions

Spanner has several JSONB functions that are not available in native PostgreSQL. You must use the spanner namespace to call these functions.

Function	Example / Notes	Description
spanner.jsonb_query_array(jsonb)	spanner.jsonb_query_array('[1, "abc", {"k": "v"}]'::jsonb) -> [1, "abc", {"k": "v"}]	Returns an array of jsonb values from a jsonb array. Similar to jsonb_array_elements in native PostgreSQL, except that it returns an array of values rather than a set of values.

JSONB operators

Operator	Example / Notes	Description
->	'{"a": {"b":"bear"}}'::jsonb -> 'a' → {"b": "bear"} '[{"a":"apple"},{"b":"bear"},{"c":"cat"}]'::jsonb -> 2 → {"c": "cat"} '{"a": {"b":"bear"}}'::jsonb -> 'a' -> 'b' → bear '[{"a":"apple"},{"b":"bear"},{"c":"cat"}]'::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":"bear"}}'::jsonb ->> 'a' → {"b": "bear"} '[{"a":"apple"},{"b":"bear"},{"c":"cat"}]'::jsonb ->> 2 → {"c": "cat"} '[{"a":"apple"},{"b":"bear"},{"c":"cat"}]'::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 | float4 | 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 and operators

Pattern matching functions

Function	Example / Notes	Description
like(string bytea, pattern bytea)	Returns Boolean.	Returns true if the string matches the supplied pattern. For more information about the LIKE expression, see the postgresql.org documentation.
like(string text, pattern text)	Returns Boolean.	Returns true if the string matches the supplied pattern. For more information about the LIKE expression, see the postgresql.org documentation.
regexp_match(string text, pattern text)	regexp_match('exambarbequeple','(bar)(beque)') → {'bar', 'beque'}	Returns an array of matching substrings within the first match of a POSIX regular expression pattern to a string. If there is no match, then the result is NULL. If there is a match, and the pattern contains parenthesized subexpressions, then the result is a text array whose nth element is the substring matching the nth parenthesized subexpression of the pattern (not counting non-capturing parentheses).
regexp_match(string text, pattern text, flags text)	regexp_match('examBaRBeQUEple','(bar)(beque)', 'i') → {'BaR', 'BeQUE'}	Returns an array of matching substrings within the first match of a POSIX regular expression pattern to a string. If there is no match, the result is NULL. If a match is found and the pattern contains parenthesized subexpressions, then the result is a text array whose nth element is the substring matching the nth parenthesized subexpression of the pattern (not counting non-capturing parentheses). The flags parameter contains zero or more single-letter flags that change the function's behavior. For more information about using flags, see the open-source PostgreSQL Embedded-option Table documentation.
regexp_split_to_array(string text, pattern text)	regexp_split_to_array('the quick brown fox jumps over the lazy dog','\s+') → {'the','quick',''brown','fox','jumps','over','the','lazy','dog'}	Splits a string using a POSIX regular expression pattern as a delimiter. If there is no match to the pattern, the function returns the string. If there is at least one match, then for each match, the function returns the text from the end of the last match (or the beginning of the string) to the beginning of the match. When there are no more matches, the function returns the text from the end of the last match to the end of the string.
regexp_split_to_array(string text, pattern text, flags text)	regexp_split_to_array('​thE QUick bROWn FOx jUMPs ovEr The lazy dOG','e', 'i') → {'th',' QUick bROWn FOx jUMPs ov','r Th',' lazy dOG'}	Splits a string using a POSIX regular expression pattern as a delimiter. If there is no match to the pattern, then the function returns the string. If there is at least one match, then for each match, the function returns the text from the end of the last match (or the beginning of the string) to the beginning of the match. When there are no more matches, the function returns the text from the end of the last match to the end of the string. The flags parameter contains zero or more single-letter flags that change the function's behavior. For more information about using flags, see the open-source PostgreSQL Embedded-option Table documentation.

Pattern matching operators

Operator	Example / Notes	Description
string text !~ pattern text	'thomas' !~ 't.*max' → true	Tests whether a string text does not match a regular expression. Case sensitive.

Formatting functions

Function	Example / Notes	Description
to_char(number int8, format text)	to_char(125, '999') → 125	Converts int8 to string according to the given format.^[1]
to_char(number numeric, format text)	to_char(-125.8, '999D99S') → 125.8-	Converts numeric to string according to the given format.^[1]
to_char(number float8, format text)	to_char(125.8::float8, '999D9') → 125.8	Converts float8 to string according to the given format.^[1]
to_char(timestamp timestamptz, format text)	to_char(timestamp '2002-04-20 17:31:12.66', 'HH12:MI:SS') → 05:31:12	Converts timestamptz to string according to the given date format.^[2]
to_number(number text, format text)	to_number('12,454.8-', '99G999D9S') → -12454.8	Converts string to numeric according to the given format.^[1]
to_date(date text, format text)	to_date('05 Dec 2000', 'DD Mon YYYY') → 2000-12-05	Converts string to date according to the given date format.^[2]
to_timestamp(timestamp timestamptz, format text)	to_timestamp('05 Dec 2000', 'DD Mon YYYY') → 2000-12-05 00:00:00-05	Converts string to timestamptz format according to the given date time format.^[2]

^[1]For a list of supported numeric formatting, see Template Patterns for Numeric Formatting.

^[2]For a list of supported date/time formatting, see Supported formats for date data type and Supported formats for timestamptz data type.

Sequence functions

Function	Example / Notes	Description
nextval (varchar) → bigint	nextval ('MySequence')	Takes a sequence name string and returns the next sequence value in the bigint data type. This function is only allowed in read-write transactions
spanner.get_internal_sequence_state(varchar)	spanner.get_internal_sequence_state('MySequence')	Gets the current sequence internal counter before bit reversal. As the sequence generates values, its internal counter changes. This function is useful when using import or export, and for migrations. If nextval ('MySequence') is never called on the sequence, then this function returns NULL.

Utility functions

Function	Example / Notes	Description
spanner.generate_uuid()	SELECT spanner.generate_uuid() AS uuid → 4192bff0-e1e0-43ce-a4db-912808c32493	Returns a random universally unique identifier (UUID) (Version 4) as a string. that Spanner can use for primary key columns. The returned string consists of 32 hexadecimal digits in five groups separated by hyphens in the form 8-4-4-4-12. The hexadecimal digits represent 122 random bits and 6 fixed bits, in compliance with RFC 4122 section 4.4. The returned string is lowercase.