Supported PostgreSQL operators

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

An operator manipulates any number of data inputs, also called operands, andreturns 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 willbe taken as the start of a comment.

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

    ~ ! @ # % ^ & | ` ?

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

Operator Precedence

The following table shows the precedence and associativity of the operators inPostgreSQL. Most operators have the same precedence and areleft-associative. The precedence and associativity of the operators ishard-wired into the parser. Enclose expressions in parentheses to force aspecific evaluation order.

Operator Precedence (highest to lowest)

Operator/ElementAssociativityDescription
.lefttable/column name separator
::leftPostgreSQL-style typecast
[]leftarray element selection
+-rightunary plus, unary minus
^leftexponentiation
*/%leftmultiplication, division, modulo
+-leftaddition, subtraction
(any other operator)leftall other PostgreSQL and user-defined operators
BETWEENLIKEIN range containment, string matching, set membership
<>=<=>=<> comparison operators
ISISNULLNOTNULL IS TRUE,IS FALSE,IS NULL,IS DISTINCT FROM, and more
NOTrightlogical negation
ANDleftlogical conjunction
ORleftlogical disjunction

Array operators

OperatorExample/NotesDescription
@>array[1, 2, 3] @> array[1, 2, 1] → trueArray contains operator. Returnstrue 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] → trueArray contained operator. Returnstrue 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] → trueArray overlap operator. Returnstrue 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

OperatorExample / NotesDescription
date - datedate '2001-10-01' - date '2001-09-28' → 3Subtracts dates, returning the number of days that have elapsed.
date - integerdate '2001-10-01' - 7 → 2001-09-24Subtracts a number of days from a date, returning the new date.
date + integerdate '2001-09-28' + 7 → 2001-10-05Adds a number of days to a date, returning the new date.

JSONB operators

OperatorExample / NotesDescription
->

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

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

When the argument is an integern, thenth element of ajsonb 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, ajsonb object field is extracted with the given key.

When the argument is an integern, thenth element of ajsonb 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 twojsonb 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 ajsonb object, or matching string values from ajsonb 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

OperatorExample / NotesDescription
string text !~pattern text'thomas' !~ 't.*max' → trueTests whether a string text does not match a regular expression. Case sensitive.

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

Last updated 2026-02-19 UTC.