Supported PostgreSQL functions (original) (raw)

Skip to main content

Supported PostgreSQL functions

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

Mathematical functions

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

Function Example/Notes Description
abs(float4 | 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.
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. Whenpreserve_sign is true, this function provides the same bit-reversal algorithm used in bit-reversed sequence. SeeBit-reversed sequence.
spanner.approx_cosine_distance(float4[], float4[] [, options=>'{value}'] | float8[], float8[] [, options=>'{value}']) spanner.approx_cosine_distance('{1.0, 2.0}'::float4[], '{3.0, 4.0}'::float4[]) spanner.approx_cosine_distance('{2.0, 1.0}'::float8[], '{4.0, 3.0}'::float8[]) spanner.approx_cosine_distance('{1.0, 2.0}'::float4[], '{3.0, 4.0}'::float4[], options=>'{"num_leaves_to_search": 1000}') Computes the approximate cosine distance between two vectors. Approximation typically occurs when using specific indexing strategies that precompute clustering, using approximate cosine distances. Approximation is non-deterministic and volatile. You must create a vector index first before using this function. For more information, see Create vector index. 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, Spanner produces an error. Spanner produces an error 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 by the vector index, Spanner produces an error. If either of the arguments is null, null is returned. options is a named argument with a value that represents a Spanner-specific optimization. value must be"num_leaves_to_search": int4. This option specifies the approximate nearest neighbors (ANN) algorithm configuration used in your query. The total number of leaves is specified when you create your vector index. A larger value improves recall, but is less performant. Some experimentation might be required for specific use cases. We recommend using a number that's 1% the total number of leaves defined in the CREATE INDEX statement to start. The number of leaves to search is defined by thenum_leaves_to_search option for both 2-level and 3-level trees. If an unsupported option is provided, an error is produced.
spanner.approx_dot_product(float4[], float4[] [, options=>'{value}'] | float8[], float8[] [, options=>'{value}']) spanner.approx_dot_product('{100, 10}'::float4[], '{200, 6}'::float4[]) spanner.approx_dot_product('{100, 10}'::float4[], '{200, 6}'::float4[], options=>'{"num_leaves_to_search": 1000}') Computes the approximatedot product between two vectors. The dot product is computed by summing the product of corresponding vector elements. Approximation typically occurs when using specific indexing strategies that precompute clustering, using approximate dot products. Approximation is non-deterministic and volatile. You must create a vector index first before using this function. For more information, seeCreate vector index. 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, Spanner produces an error. Spanner produces an error if an element or field in a vector is null. If either of the arguments is null, null is returned. options is a named argument with a value that represents a Spanner-specific optimization. value must be"num_leaves_to_search": int4. This option specifies the approximate nearest neighbors (ANN) algorithm configuration used in your query. The total number of leaves is specified when you create your vector index. A larger value improves recall, but is less performant. Some experimentation might be required for specific use cases. We recommend using a number that's 1% the total number of leaves defined in the CREATE INDEX statement to start. The number of leaves to search is defined by thenum_leaves_to_search option for both 2-level and 3-level trees. If an unsupported option is provided, an error is produced.
spanner.approx_euclidean_distance(float4[], float4[] [, options=>'{value}'] | float8[], float8[] [, options=>'{value}']) spanner.approx_euclidean_distance('{1.0, 2.0}'::float4[], '{3.0, 4.0}'::float4[]) spanner.approx_euclidean_distance('{2.0, 1.0}'::float8[], '{4.0, 3.0}'::float8[]) spanner.approx_euclidean_distance('{1.0, 2.0}'::float4[], '{3.0, 4.0}'::float4[], options=>'{"num_leaves_to_search": 1000}') Computes the approximateEuclidean distance between two vectors. Approximation typically occurs when using specific indexing strategies that precompute clustering, using approximate Euclidean distances. Approximation is non-deterministic and volatile. However, the result might be the exact EUCLIDEAN_DISTANCE, or it might have engine-specific approximations applied, depending on the options and the schema configuration. You must create a vector index first before using this function. For more information, seeCreate vector index. 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, Spanner produces an error. Spanner produces an error if an element or field in a vector is null. If either of the arguments is null, null is returned. options is a named argument with a value that represents a Spanner-specific optimization. value must be"num_leaves_to_search": int4. This option specifies the approximate nearest neighbors (ANN) algorithm configuration used in your query. The total number of leaves is specified when you create your vector index. A larger value improves recall, but is less performant. Some experimentation might be required for specific use cases. We recommend using a number that's 1% the total number of leaves defined in the CREATE INDEX statement to start. The number of leaves to search is defined by thenum_leaves_to_search option for both 2-level and 3-level trees. If an unsupported option is provided, an error is produced.
spanner.cosine_distance(float4[], float4[] | 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 isnull. 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.
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 isnull. 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 either of the arguments is null, null is returned.
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 isnull. 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.
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 and comparisons

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.
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, seeWorking 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 is1. For more information, seeWorking with arrays in PostgreSQL-dialect databases.
array(subquery) Returns an ARRAY with one element for each row in the subquery. For more information, seeWorking with arrays in PostgreSQL-dialect databases.
unnest(anyarray) Expands an ARRAY into a set of rows. For more information, seeWorking with arrays in PostgreSQL-dialect databases.

Array comparisons

Comparison syntax Example Description
expression = ANY (anyarray) expression = SOME (anyarray) 2 = ANY(array[1, 2]) → true ANY/SOME array comparison construct. Returnstrue if the evaluated value of the expression on the left is equal to any of the array elements. There are no differences betweenANY and SOME. ANY/SOME only supports the = operator.
expression operator ALL (anyarray) (1+2) >= ALL(array[1, 2, 3]) → true ALL array comparison construct. Returns true if the expression on the left evaluates to true when compared against all elements of the array with the specified operator. = , <> , > , >= , < , and <= operators are supported with ALL.

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 results 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 PostgreSQLPOSIX 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 the_string_.
spanner.soundex(text) spanner.soundex('Ashcraft') -> A261 Returns a STRING that represents theSoundex code for value.Soundex produces a phonetic representation of a string. It indexes words by sound, as pronounced in English. It's typically used to help determine whether two strings have similar English-language pronunciations, such as the family names Levine and Lavine, or the words_to_ and too, The result of the Soundex consists of a letter followed by 3 digits. Non-latin characters are ignored. If the remaining string is empty after removing non-Latin characters, an empty string is returned.
spanner.split_substr(value text, delimiter text, start_split int8[, count int8]) spanner.split_substr('www.abc.xyz.com', '.', 1, 0) → '' spanner.split_substr('www.abc.xyz.com', '.', 1, 2) → www.abc spanner.split_substr('www.abc.xyz.com', '.', 1, 1) → www spanner.split_substr('www.abc.xyz.com', '.', -1, 1) → com spanner.split_substr('www.abc.xyz.com', '.', 2, 2) → abc.xyz Returns substrings from value that is determined by the delimiter, with start_split indicating the first split of the substring and count indicating the number of splits to be returned. value is the supplied text value from which a substring is returned. delimiter must be a literal character or a sequence of characters that is matched from left to right against value. It can't be a regular expression. If the delimiter is a sequence of characters, then two instances of the delimiter in_value_ can't overlap. start_split is an integer that specifies the first split of the substring to return. If start_split is 1,0, or less than the negative of the split count, then the function returns a substring that starts with the first split. If the value is greater than the number of splits, the function returns an empty string. If the value is negative, then the splits are counted from the end of the input string. count is an integer that can be optionally specified to determine the maximum number of splits to include in the returned substring. If count is unspecified or if the sum of_count_ and start_split is greater than the split count, the function returns the substring from the_start_split_ position to the end of value. If count is 0, the function returns an empty string. If count is negative, the function returns an error.
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 PostgreSQLPOSIX 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.

Hash functions

Function Example / Notes Description
spanner.farm_fingerprint(value bytea | text) spanner.farm_fingerprint('abc') → 2640714258260161385 Computes the fingerprint of value using the FarmHash Fingerprint64 algorithm.

Date and time functions

This section describes the date and time functions that are available in Spanner.

Date and 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. current_timestamp is a stable function. This behavior means that the function returns the same timestamp value throughout the execution of a single SQL statement. Subsequent statements return an updated value. For more information, see Function volatility.
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 and time values and returns values of type numeric. Source can use the date or timestamptz data type.
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) totimestamptz format.

Spanner specific date and time functions

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

Function Example / Notes Description
spanner.date(timestamptz, text) spanner.date('2025-04-14 03:38:40+00'::timestamptz, 'America/New_York') -> 2025-04-13 Extracts date from a timestamptz in a specified time zone. If a time zone value is not provided in the first parameter, the time zone value defaults to America/Los_Angeles. For example, spanner.date('2025-04-14 23:38:40'::timestamptz, 'America/New_York') -> 2025-04-15
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.pending_commit_timestamp() UPDATE Performances SET LastUpdated = spanner.pending_commit_timestamp() Uses the PENDING_COMMIT_TIMESTAMP() function in a DML INSERT or UPDATE statement to write the pending commit timestamp of the write when it commits into a column type SPANNER.COMMIT_TIMESTAMP.You can also use SPANNER.PENDING_COMMIT_TIMESTAMP() as a default value and ON UPDATE value, but only if you useSPANNER.COMMIT_TIMESTAMP with it. If you setSPANNER.PENDING_COMMIT_TIMESTAMP() as the default value, its value is used in the insert statement if no value is provided. If you also set SPANNER.PENDING_COMMIT_TIMESTAMP() as the ON UPDATE value, its value is used in the update statement if no value is provided.
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. To be more consistent with the PostgreSQL language, we recommend using theINTERVAL type with the addition operator (+) instead.
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. To be more consistent with the PostgreSQL language, we recommend using theINTERVAL type with the subtraction operator (-) instead.

Search functions

Spanner has several functions that perform full-text search operations. For more information, seeFull-text search. For more information on search functions, see the GoogleSQLSearch functionssection.

Indexing

Functions that you can use to create search indexes.

Function Example / Notes Description
spanner.token(value text/bytea) Returns a spanner.tokenlist Constructs an exact match tokenlist value by tokenizing a text value verbatim to accelerate exact match expressions.
spanner.tokenize_bool( value bool) Returns a spanner.tokenlist. Constructs a boolean tokenlist value by tokenizing aBOOL value to accelerate boolean match expressions.
spanner.tokenize_fulltext( value text/text[] [, language_tag text] [, content_type text] [, token_category text]) Returns a spanner.tokenlist. Constructs a full-text tokenlist value by tokenizing text for full-text matching.
spanner.tokenize_jsonb( value jsonb) Returns a spanner.tokenlist. Constructs a JSON tokenlist value by tokenizing aJSONB value to accelerate JSON predicates.
spanner.tokenize_ngrams( value text [, ngram_size_min int8] [, ngram_size_max int8] [, remove_diacritics bool]) Returns a spanner.tokenlist. Constructs an n-gram tokenlist value by tokenizing a text value for matching n-grams.
spanner.tokenize_number( value int8 [, comparison_type text] [, algorithm text] [, min int8] [, max int8] [, granularity int8] [, tree_base int8] [, ieee_precision int8]) Returns a spanner.tokenlist. Constructs a numeric tokenlist value by tokenizing numeric values to accelerate numeric comparison expressions.
spanner.tokenize_substring( value text/text[] [, language_tag text] [, ngram_size_min int8] [, ngram_size_max int8] [, relative_search_types text[]] [, content_type text] [, short_tokens_only_for_anchors bool] [, remove_diacritics bool]) Returns a spanner.tokenlist. Constructs a substring tokenlist value by tokenizing text for substring matching.
spanner.tokenlist_concat(tokens spanner.tokenlist[]) Returns a spanner.tokenlist. Displays a human-readable representation of tokens present in atokenlist value for debugging purposes.

Retrieval and presentation

Functions that you can use to search for data, score the search result, or format the search result.

Function Example / Notes Description
spanner.score( tokens spanner.tokenlist, query text [, dialect text] [, language_tag text] [, enhance_query bool] [, dictionary text] [, options jsonb]) Returns a float8. Calculates a relevance score of a tokenlist for a full-text search query. The higher the score, the stronger the match.
spanner.score_ngrams( tokens spanner.tokenlist, ngrams_query text [, language_tag text] [, algorithm text]) Calculates the relevance score of a tokenlist for a fuzzy search. The higher the score, the stronger the match.
spanner.search( tokens spanner.tokenlist, query text [, dialect text] [, language_tag text] [, enhance_query bool] [, dictionary text]) Returns a bool. Returns TRUE if a full-text search query matches tokens.
spanner.search_ngrams( tokens spanner.tokenlist, ngrams_query text [, language_tag text] [, min_ngrams int8] [, min_ngrams_percent float8]) Returns a bool. Checks whether enough n-grams match the tokens in a fuzzy search.
spanner.search_substring( tokens spanner.tokenlist [, query text] [, language_tag text] [, relative_search_type text]) Returns a bool. Returns TRUE if a substring query matches tokens.
spanner.snippet( value text, query text [, language_tag text] [, enhance_query bool] [, dictionary text] [, max_snippet_width int8] [, max_snippets int8] [, content_type text]) Returns jsonb. Gets a list of snippets that match a full-text search query.

Debugging

Functions that you can use for debugging.

Function Example / Notes Description
spanner.debug_tokenlist( spanner.tokenlist) Returns text. Displays a human-readable representation of tokens present in thetokenlist value for debugging purposes.

JSONB functions

Spanner supports several JSONB functions.

For more information, see thePostgreSQL JSONB documentation.

JSONB functions

| Function | Example / Notes | Description | | | | | | | | | --------------------------------------------------------------------------------------------------------------------------------------- | --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | --- | | ------- | | ------------ | -------------------------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ | | jsonb_array_elements(JSONB) | jsonb_array_elements('[1, "abc", {"k": "v"}]'::jsonb) /*---------------------* | jsonb_array_elements | +----------------------+ | '1' | | '"abc"' | | '{"k": "v"}' | *---------------------*/ | Expands a jsonb array to a set of jsonb values. Returns multiple rows, with one element per row. Unlike open source PostgreSQL, this can only be called as a table valued function in the FROM clause. | | 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. | | | | | | | | | jsonb_insert(target jsonb, path text[], new_value jsonb [, insert_after bool]) → jsonb | jsonb_insert('{"a": [0,1,2]}', '{a, 1}', '"new_value"') → {"a": [0, "new_value", 1, 2]} jsonb_insert('{"a": [0,1,2]}', '{a, 1}', '"new_value"', true) → {"a": [0, 1, "new_value", 2]} | Returns target with new_value inserted as specified by path. If the item designated by the path is an array element, new_value is inserted before that item if insert_after is false (which is the default behavior), or after it if insert_after is true. If the item designated by the path is an object field, new_value is inserted only if the object does not already contain that key. All earlier steps in the path must exist, or the target is returned unchanged. As with the path-oriented operators, negative integers that appear in the path count from the end of JSON arrays. If the last path step is an array index that is out of range, the new value is added at the beginning of the array if the index is negative or at the end of the array if it is positive. | | | | | | | | | jsonb_set(target jsonb, path text[], new_value jsonb [, create_if_missing bool]) → jsonb | jsonb_set('[{"f1":1,"f2":null},2,null,3]', '{0,f1}', '[2,3,4]', false) → [{"f1": [2, 3, 4], "f2": null}, 2, null, 3] jsonb_set('[{"f1":1,"f2":null},2]', '{0,f3}', '[2,3,4]') → [{"f1": 1, "f2": null, "f3": [2, 3, 4]}, 2] | Returns target with the item designated by path replaced bynew_value, or with new_value added ifcreate_if_missing is true (which is the default behavior) and the item designated by the path does not exist. All earlier steps in the path must exist, or the target is returns unchanged. As with the path-oriented operators, negative integers that appear in the path count from the end of JSON arrays. If the last path step is an array index that is out of range. Ifcreate_if_missing is true and the last path step is an out-of-range array index, the new value is added to the beginning of the array (if the index is negative) or the end of the array (if the index is positive). | | | | | | | | | jsonb_set_lax(target jsonb, path text[], new_value jsonb [, create_if_missing bool [, null_value_treatment text]]) → jsonb | jsonb_set_lax('[{"f1":1,"f2":null},2,null,3]', '{0,f1}', null) → [{"f1": null, "f2": null}, 2, null, 3] jsonb_set_lax('[{"f1":99,"f2":null},2]', '{0,f3}', null, true, 'return_target') → [{"f1": 99, "f2": null}, 2] | If new_value is not NULL, this function behaves identically to jsonb_set. Otherwise, this function behaves according to the value of null_value_treatment which must be one of raise_exception, use_json_null,delete_key, or return_target. The default is use_json_null. jsonb_set_lax has the same behavior as jsonb_set unless the null_value_treatment parameter is a NULLnull, then this function returns an error. If the new_value parameter is a SQL NULL then the jsonb_set_lax function returns behavior based on the null_value_treatment parameter. | | | | | | | | | jsonb_strip_nulls(jsonb) → jsonb | jsonb_strip_nulls('[{"f1":1, "f2":null}, 2, null, 3]') → [{"f1": 1}, 2, null, 3] | Deletes all object fields that have null values from a defined JSON array, recursively. Does not affect null values outside of object fields. | | | | | | | | | 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_object_keys(jsonb) | jsonb_object_keys('{"a":1, "b":{"c":1, "d":4}}'::jsonb) → {"a", "b"} | Returns a set of keys in the top-level jsonb object as a set of string values. Unlike in open source PostgreSQL, this function can only be called in theFROM clause. The keys are de-duplicated and returned in length-first lexicographic order. | | | | | | | | | 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, andNULL. The null result shouldn't be confused with a SQL NULL, as the examples illustrate. | | | | | | | |

Spanner specific JSONB functions

Spanner has several JSONB functions that are not available in open source PostgreSQL. You must use the spannernamespace to call these functions.

Function Example / Notes Description
spanner.bool_array(jsonb) spanner.bool_array('[true, false]'::jsonb) → [true, false] spanner.bool_array('["true"]'::jsonb) → ERROR Returns an array of boolean values from a jsonb array. Raises an error if the argument is not an array of boolean values.
spanner.float32_array(jsonb) spanner.float32_array('[1, -2, 3.0]'::jsonb) → [1.0, -2.0, 3.0] spanner.float32_array('[1e100]'::jsonb) → ERROR Returns an array of real values from a jsonb array. Raises an error if the argument is not an array of number values inreal domain.
spanner.float64_array(jsonb) spanner.float64_array('[1, -2, 3.0]'::jsonb) → [1.0, -2.0, 3.0] spanner.float64_array('[1e100]'::jsonb) → ERROR Returns an array of real values from a jsonb array. Raises an error if the argument is not an array of number values indouble precision domain.
spanner.int64_array(jsonb) spanner.int64_array('[1, -2, 3.0]'::jsonb) → [1, -2, 3] spanner.int64_array('[1.1]'::jsonb) → ERROR Returns an array of int8 values from a jsonb array. Raises an error if the argument is not an array of number values inint8 domain.
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 tojsonb_array_elements in PostgreSQL, except that it returns an array of values rather than a set of values.
spanner.string_array(jsonb) spanner.string_array('["a", "b", "c"]'::jsonb) → ['a', 'b', 'c'] spanner.string_array('[null]'::jsonb) → ERROR Returns an array of text values from a jsonb array. Raises an error if the argument is not an array of string values.

Interval functions

Function Example / Notes Description
make_interval ( [ years int8 [, months int8 [, weeks int8 [, days int8 [, hours int8 [, mins int8 [, secs double precision ]]]]]]] ) → interval SELECT make_interval(years => 1, months => 2, weeks => 3, days => 15, hours => 10, mins => 30, secs => 15.1) -> P1Y2M36DT10H30M15.1S Creates an interval from years, months, weeks, days, hours, minutes, and seconds fields. The default value for each of the fields is 0.
-interval SELECT -INTERVAL '1 year 2 months 15 days 45 seconds 500 microseconds'; Result: P-1Y-2M-15DT-45.0005S Negate an interval.
interval + interval → interval SELECT INTERVAL '1 year 2 months 15 days' + INTERVAL '1 hour 15 minutes 45 seconds 500 milliseconds'; Result: P1Y2M15DT1H15M45.5S Add intervals.
interval - interval → interval SELECT INTERVAL '1 year 2 months 10 hours 30 minutes' - INTERVAL '15 days 45 seconds 500 microseconds'; Result: P1Y2M-15DT10H29M14.9995S Subtract intervals.
interval * double precision → interval SELECT INTERVAL '4 months 12 days 20 seconds' * 4.0; Result: P1Y4M48DT1M20S Multiply an interval by a scalar.
interval / double precision → interval SELECT INTERVAL '1 hour' / 2.5; Result: PT24M Divide an interval by a scalar.
timestamptz + interval → timestamptz SELECT TIMESTAMP WITH TIME ZONE '2021-12-18T10:00:00+00' + INTERVAL '2 months 15 days 40 minutes'; Result: 2022-03-05T10:40:00Z Add an interval to a timestamp with a time zone.
timestamptz - interval → timestamptz SELECT TIMESTAMP WITH TIME ZONE '2024-12-18T10:00:00+00' - INTERVAL '2 months 15 days 40 minutes'; Result: 2024-10-03T08:20:00Z Subtract an interval from a timestamp with a time zone.
timestamptz - timestamptz → interval SELECT TIMESTAMPTZ '2024-12-18T10:00:00+00' - TIMESTAMPTZ '2024-10-03T08:20:00Z' Result: PT1825H40M Subtract timestamps with a time zone. Unlike open source PostgreSQL, Spanner doesn't convert 24 hour time periods into days.
justify_hours(interval) → interval SELECT justify_hours(interval '50 hours 10 minutes') Result: P2DT2H10M SELECT justify_hours(interval '-12 day 50 hours 10 minutes') Result: P-9DT-21H-50M Normalizes 24-hour time periods into full days. Adjusts time and days to have the same sign.
justify_days(interval) → interval SELECT justify_days(interval '45 days 50 hours 10 minutes') Result: P1M15DT50H10M SELECT justify_days(interval '-1 year 45 days') Result: P-10M-15D Normalizes 30-day time periods into full months. Adjusts days and months to have the same sign.
justify_interval(interval) → interval SELECT justify_interval(INTERVAL '29 days 60 hours') Result: P1M1DT12H SELECT justify_interval(INTERVAL '-34 days 60 hours') Result: P-1M-1DT-12H Normalizes 24-hour time periods into full days, then 30-day time periods into full months. Adjusts all parts to have the same sign.
extract(field FROM source) → numeric SELECT extract(SECOND FROM INTERVAL '1 year 2 months 15 days 10 hours 30 minutes 15 seconds 100 milliseconds') Result: 15.100000 Retrieves subfield from an interval value and returns a value of type numeric.

Aggregate functions

Function Example / Notes Description
array_agg(anynonarray [ORDER BY input_sort_columns]) Inserts the given values, including nulls, into an array.input_sort_columns, if specified, must have the same syntax as a query-level ORDER BY clause and is used to sort the inputs.
avg(float4 | float8 interval 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 interval 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(float4 | float8 interval int8 numeric text timestamptz) Returns same type as input type. Computes the maximum of the non-null input values.
min(float4 | float8 interval 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 [ORDER BY input_sort_columns]) 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). input_sort_columns, if specified, must have the same syntax as a query-level ORDER BY clause and is used to sort the inputs.
sum(float4 | float8 interval 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 use the same data type. The result has the same data type.
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 use the same data type. The result has the same data type. NULL values in the list are ignored. The result is 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 use the same data type. The result will have the same data type. NULL values in the list are ignored. The result is 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 use comparable types. Specifically, 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 subtle difference. What is actually returned is the first argument of the implied = operator, and in some cases that is promoted to match the second argument's type. For example, NULLIF(1, 2.2) yields a numeric, because there is no integer = numeric operator, only numeric = numeric.
ISNULL datatype ISNULL → boolean Tests whether value is null (non-standard syntax).
NOTNULL datatype NOTNULL → boolean Tests whether value is not null (non-standard 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

This section describes the pattern matching functions that are available in Spanner.

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 thepostgresql.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 thepostgresql.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_n_th element is the substring matching the _n_th 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_n_th element is the substring matching the _n_th 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 PostgreSQLEmbedded-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 PostgreSQLEmbedded-option Table documentation.

Formatting functions

Function Example / Notes Description
to_char(interval_value interval, format text) SELECT to_char(INTERVAL '1 year 2 months 15 days 10 hours 30 minutes 15 seconds 100 milliseconds', 'YYYY-MM-DD HH24:MI:SS.MS'); Converts interval to string according to the given date format.[1]
to_char(number int8, format text) to_char(125, '999') → 125 Converts int8 to string according to the given format.[2]
to_char(number numeric, format text) to_char(-125.8, '999D99S') → 125.8- Converts numeric to string according to the given format.[2]
to_char(number float4, format text) to_char(125.8::float4, '999D9') → 125.8 Converts float4 to string according to the given format.[2]
to_char(number float8, format text) to_char(125.8::float8, '999D9') → 125.8 Converts float8 to string according to the given format.[2]
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.[3]
to_number(number text, format text) to_number('12,454.8-', '99G999D9S') → -12454.8 Converts string to numeric according to the given format.[2]
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.[3]
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.[3]

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. Ifnextval ('MySequence') is never called on the sequence, then this function returns NULL.

Set returning functions

This section describes functions that possibly return more than one row.

| generate_series(start int8 | numeric, end int8 | numeric [, step int8 | numeric]) | SELECT * FROM generate_series(2, 4) /*---------------------* | generate_series | +----------------------+ | 2 | | 3 | | 4 | *---------------------*/ SELECT * FROM generate_series(3, 0, -2) /*---------------------* | generate_series | +----------------------+ | 3 | | 1 | *---------------------*/ | Generates a series of values. The function accepts two arguments: the start value and the end value. An optional third_step_ argument specifies the increment between the first two arguments (default is 1). This function generates a set of rows, with each row representing a value from the series. The series begins at the start value and includes values up to and including the end value, or until the final increment is reached. | | ---------------------------------------------------- | ----------------------- | ---------- | ------------------------------------------------------------------------------------------------ | ------------------------ | - | | - | | - | ----------------------------------------------------------------------------------------------------------- | ---------------- | ------------------------ | - | | - | -------------------------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ |

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.
gen_random_uuid() gen_random_uuid() -> uuid Returns a random universally unique identifier (UUID) (Version 4).

Except as otherwise noted, the content of this page is licensed under the Creative Commons Attribution 4.0 License, and code samples are licensed under the Apache 2.0 License. For details, see the Google Developers Site Policies. Java is a registered trademark of Oracle and/or its affiliates.

Last updated 2026-06-15 UTC.