Supported PostgreSQL operators

This page describes the operators supported for PostgreSQL-dialect databases in Spanner.

An operator manipulates any number of data inputs, also called operands, and returns a result.

An operator name is a sequence of characters from the following list:

  • - * / < > = ~ ! @ # % ^ & | ` ?

There are a few restrictions on operator names:

  • - and /* cannot appear anywhere in an operator name, since they will be taken as the start of a comment.

  • A multiple-character operator name cannot end in + or -, unless the name also contains at least one of these characters:

    ~ ! @ # % ^ & | ` ?

    For example, @- is an allowed operator name, but \*- is not. This restriction allows PostgreSQL to parse SQL-compliant queries without requiring spaces between tokens.

Operator Precedence

The following table shows the precedence and associativity of the operators in PostgreSQL. Most operators have the same precedence and are left-associative. The precedence and associativity of the operators is hard-wired into the parser. Enclose expressions in parentheses to force a specific evaluation order.

Operator Precedence (highest to lowest)

Operator/Element Associativity Description
. left table/column name separator
:: left PostgreSQL-style typecast
[ ] left array element selection
+ - right unary plus, unary minus
^ left exponentiation
* / % left multiplication, division, modulo
+ - left addition, subtraction
(any other operator) left all other PostgreSQL and user-defined operators
BETWEEN LIKE IN   range containment, string matching, set membership
< > = <= >= <>   comparison operators
IS ISNULL NOTNULL   IS TRUE, IS FALSE, IS NULL, IS DISTINCT FROM, and more
NOT right logical negation
AND left logical conjunction
OR left logical disjunction

Array operators

Operator Example/Notes Description
@> array[1, 2, 3] @> array[1, 2, 1] → true Array contains operator. Returns true if the first array contains the second, that is, if every element in the second array equals some element in the first array.
<@ array[1, 1, 3] <@ array[1, 2, 3, 4] → true Array contained operator. Returns true if the second array contains the first array. That is, if every element in the first array equals some element in the second array.
&& array[1, 2, 3] && array[1, 5] → true Array overlap operator. Returns true if the elements in the arrays overlap, that is, if they have any element in common.
|| array[1, 2] || array[3, 4] → {1, 2, 3, 4} Concatenation operator. Concatenates two arrays.

Date and 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 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.
@>

'{"a":1, "b":2}'::jsonb @> '{"b":2}'::jsonb → true

Tests whether the left JSONB value contains the right JSONB value.
<@

'{"b":2}'::jsonb <@ '{"a":1, "b":2}'::jsonb → true

Tests whether the left JSONB value is contained in the right JSONB value.
?

'{"a":1, "b":2}'::jsonb ? 'b' → true

'["a", "b", "c"]'::jsonb ? 'b' → true

Tests whether a text string exists as a top-level key or array element within a JSONB value.
?|

'{"a":1, "b":2, "c":3}'::jsonb ?| array['b', 'd'] → true

Tests whether any of the strings in a text array exist as top-level keys or array elements.
?&

'["a", "b", "c"]'::jsonb ?& array['a', 'b'] → true

Tests whether all of the strings in a text array exist as top-level keys or array elements.
jsonb || jsonb → jsonb

'["a", "b"]'::jsonb || '["a", "d"]'::jsonb → ["a", "b", "a", "d"]

'{"a": "b"}'::jsonb || '{"c": "d"}'::jsonb → {"a": "b", "c": "d"}

'[1, 2]'::jsonb || '3'::jsonb → [1, 2, 3]

'{"a": "b"}'::jsonb || '42'::jsonb → [{"a": "b"}, 42]

To append an array to another array as a single entry, wrap it in an additional array layer: '[1, 2]'::jsonb || jsonb_build_array('[3, 4]'::jsonb) → [1, 2, [3, 4]]

Concatenates two jsonb values. Concatenating two arrays generates an array containing all the elements of each input. Concatenating two objects generates an object containing the union of their keys, taking the second object's value when there are duplicate keys. All other cases are treated by converting a non-array input into a single-element array, and then processing them as two separate arrays. Does not operate recursively; only merges the top-level array or object structure.
jsonb - text → jsonb

'{"a": "b", "c": "d"}'::jsonb - 'a' → {"c": "d"} '["a", "b", "c", "b"]'::jsonb - 'b' → ["a", "c"]

Deletes a key and its value from a jsonb object, or matching string values from a jsonb array.
jsonb - integer → jsonb

'["a", "b"]'::jsonb - 1 → ["a"]

Deletes the array element with the specified index. Negative integers are counted from the end. This function expects an array value.
jsonb #- text[] → jsonb

'["a", {"b":1}]'::jsonb #- '{1,b}' → ["a", {}]

Deletes the field or array element at the specified path, where path elements can be either field keys or array indexes.

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.