Supported PostgreSQL functions

pow is an alias of power.

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.

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.

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.

concat('abcde', 2::text, '', 22::text) → abcde222

concat('abcde', 2::text, NULL, 22::text) → NULL

textcat also concatenates text.

textcat('abcde', '222') → abcde222

textcat('abcde', NULL) → NULL

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 native PostgreSQL, this can only be called as a table valued function in the FROM clause.

jsonb_build_array(1, 'abc') → [1, "abc"]

Builds a jsonb array out of a variadic argument list.

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(1.2334000) → 1.2334000

to_jsonb(true) → true

to_jsonb('abc'::varchar) → abc

Converts the given value to 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.

'{"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.

The arguments must all be of the same data type, which will be the type of the result.

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.