Search PostgreSQL documentation

jsonb_setFunctionsReferencejsonb_set Json Functions And Operators 10 Returns target with the section designated by path replaced by new_value, or with new_value added if create_missing is true (default is true) and th…Functions · Json Functions And Operators !FunctionsPG18! boolean → boolean Boolean NOT jsonb_path_query('[1, 3, 7]', '$[*] ? (!(@ < 5))') → 7JSON Functions and Operators › The SQL/JSON Path Language › SQL/JSON Path Operators and Methods !!FunctionsPG18!! tsquery → tsquery Negates a tsquery, producing a query that matches documents that do not match the input query. !! 'cat'::tsquery → !'cat'Text Search Functions and Operators !=FunctionsPG18value != value → boolean value <> value → boolean Non-equality comparison jsonb_path_query_array('[1, 2, 1, 3]', '$[*] ? (@ != 1)') → [2, 3] jsonb_path_query_array('["a", "b", "c"]', '$[*] ?…JSON Functions and Operators › The SQL/JSON Path Language › SQL/JSON Path Operators and Methods !~FunctionsPG18text !~ text → boolean String does not match regular expression, case sensitively 'thomas' !~ 't.*max' → tPattern Matching › POSIX Regular Expressions !~*FunctionsPG18text !~* text → boolean String does not match regular expression, case-insensitively 'thomas' !~* 'T.*ma' → fPattern Matching › POSIX Regular Expressions %FunctionsPG18number % number → number Modulo (remainder) jsonb_path_query('[32]', '$[0] % 10') → 2JSON Functions and Operators › The SQL/JSON Path Language › SQL/JSON Path Operators and Methods %%FunctionsPG18%% hstore → text[] Converts hstore to an array of alternating keys and values. %% 'a=>foo, b=>bar'::hstore → {a,foo,b,bar}hstore — hstore key/value datatype › hstore Operators and Functions %>FunctionsPG18text %> text → boolean Commutator of the <% operator.pg_trgm — support for similarity of text using trigram matching › Functions and Operators %>>FunctionsPG18text %>> text → boolean Commutator of the <<% operator.pg_trgm — support for similarity of text using trigram matching › Functions and Operators &FunctionsPG18bit & bit → bit Bitwise AND (inputs must be of equal length) B'10001' & B'01101' → 00001Bit String Functions and Operators &&FunctionsPG18cube && cube → boolean Do the cubes overlap?cube — a multi-dimensional cube data type › Usage &<FunctionsPG18geometric_type &< geometric_type → boolean Does first object not extend to the right of second? Available for box, polygon, circle. box '(1,1),(0,0)' &< box '(2,2),(0,0)' → tGeometric Functions and Operators &<|FunctionsPG18geometric_type &<| geometric_type → boolean Does first object not extend above second? Available for box, polygon, circle. box '(1,1),(0,0)' &<| box '(2,2),(0,0)' → tGeometric Functions and Operators &>FunctionsPG18geometric_type &> geometric_type → boolean Does first object not extend to the left of second? Available for box, polygon, circle. box '(3,3),(0,0)' &> box '(2,2),(0,0)' → tGeometric Functions and Operators *FunctionsPG18interval * double precision → interval Multiply an interval by a scalar interval '1 second' * 900 → 00:15:00 interval '1 day' * 21 → 21 days interval '1 hour' * 3.5 → 03:30:00Date/Time Functions and Operators +FunctionsPG18date + integer → date Add a number of days to a date date '2001-09-28' + 7 → 2001-10-05Date/Time Functions and Operators -FunctionsPG18- interval → interval Negate an interval - interval '23 hours' → -23:00:00Date/Time Functions and Operators ->FunctionsPG18cube -> integer → float8 Extracts the n-th coordinate of the cube (counting from 1).cube — a multi-dimensional cube data type › Usage ->>FunctionsPG18json ->> integer → text jsonb ->> integer → text Extracts n'th element of JSON array, as text. '[1,2,3]'::json ->> 2 → 3JSON Functions and Operators › Processing and Creating JSON Data -|-FunctionsPG18anyrange -|- anyrange → boolean Are the ranges adjacent? numrange(1.1,2.2) -|- numrange(2.2,3.3) → tRange/Multirange Functions and Operators /FunctionsPG18interval / double precision → interval Divide an interval by a scalar interval '1 hour' / 1.5 → 00:40:00Date/Time Functions and Operators <FunctionsPG18value < value → boolean Less-than comparison jsonb_path_query_array('[1, 2, 3]', '$[*] ? (@ < 2)') → [1]JSON Functions and Operators › The SQL/JSON Path Language › SQL/JSON Path Operators and Methods <#>FunctionsPG18cube <#> cube → float8 Computes the taxicab (L-1 metric) distance between the two cubes.cube — a multi-dimensional cube data type › Usage <%FunctionsPG18text <% text → boolean Returns true if the similarity between the trigram set in the first argument and a continuous extent of an ordered trigram set in the second argument is greater than t…pg_trgm — support for similarity of text using trigram matching › Functions and Operators <->FunctionsPG18cube <-> cube → float8 Computes the Euclidean distance between the two cubes.cube — a multi-dimensional cube data type › Usage <->>FunctionsPG18text <->> text → real Commutator of the <<-> operator.pg_trgm — support for similarity of text using trigram matching › Functions and Operators <->>>FunctionsPG18text <->>> text → real Commutator of the <<<-> operator.pg_trgm — support for similarity of text using trigram matching › Functions and Operators <<FunctionsPG18bit << integer → bit Bitwise shift left (string length is preserved) B'10001' << 3 → 01000Bit String Functions and Operators <<%FunctionsPG18text <<% text → boolean Returns true if its second argument has a continuous extent of an ordered trigram set that matches word boundaries, and its similarity to the trigram set of the first…pg_trgm — support for similarity of text using trigram matching › Functions and Operators <<->FunctionsPG18text <<-> text → real Returns the “distance” between the arguments, that is one minus the word_similarity() value.pg_trgm — support for similarity of text using trigram matching › Functions and Operators <<<->FunctionsPG18text <<<-> text → real Returns the “distance” between the arguments, that is one minus the strict_word_similarity() value.pg_trgm — support for similarity of text using trigram matching › Functions and Operators <<=FunctionsPG18inet <<= inet → boolean Is subnet contained by or equal to subnet? inet '192.168.1/24' <<= inet '192.168.1/24' → tNetwork Address Functions and Operators <<|FunctionsPG18geometric_type <<| geometric_type → boolean Is first object strictly below second? Available for point, box, polygon, circle. box '(3,3),(0,0)' <<| box '(5,5),(3,4)' → tGeometric Functions and Operators <=FunctionsPG18value <= value → boolean Less-than-or-equal-to comparison jsonb_path_query_array('["a", "b", "c"]', '$[*] ? (@ <= "b")') → ["a", "b"]JSON Functions and Operators › The SQL/JSON Path Language › SQL/JSON Path Operators and Methods <=>FunctionsPG18cube <=> cube → float8 Computes the Chebyshev (L-inf metric) distance between the two cubes.cube — a multi-dimensional cube data type › Usage <>FunctionsPG18value != value → boolean value <> value → boolean Non-equality comparison jsonb_path_query_array('[1, 2, 1, 3]', '$[*] ? (@ != 1)') → [2, 3] jsonb_path_query_array('["a", "b", "c"]', '$[*] ?…JSON Functions and Operators › The SQL/JSON Path Language › SQL/JSON Path Operators and Methods <@FunctionsPG18cube <@ cube → boolean Is the first cube contained in the second?cube — a multi-dimensional cube data type › Usage <@>FunctionsPG18point <@> point → float8 Computes the distance in statute miles between two points on the Earth's surface.earthdistance — calculate great-circle distances › Point-Based Earth Distances <^FunctionsPG18box <^ box → boolean Is first object below second (allows edges to touch)? box '((1,1),(0,0))' <^ box '((2,2),(1,1))' → tGeometric Functions and Operators