Describes the built-in operators available in Postgres. Postgres provides a large number of built-in operators on system types. These operators are declared in the system catalog pg_operator. Every entry in pg_operator includes the name of the procedure that implements the operator and the class OIDs of the input and output types. To view all variations of the || string concatenation operator, try SELECT oprleft, oprright, oprresult, oprcode FROM pg_operator WHERE oprname = '||'; oprleft|oprright|oprresult|oprcode -------+--------+---------+------- 25| 25| 25|textcat 1042| 1042| 1042|textcat 1043| 1043| 1043|textcat (3 rows) The operators listed here are defined for a number of native data types, ranging from numeric types to data/time types. Operators Operator Description Usage < Less than? 1 < 2 <= Less than or equal to? 1 <= 2 <> Not equal? 1 <> 2 = Equal? 1 = 1 > Greater than? 2 > 1 >= Greater than or equal to? 2 >= 1 || Concatenate strings 'Postgre' || 'SQL' !!= NOT IN 3 !!= i ~~ LIKE 'scrappy,marc,hermit' ~~ '%scrappy%' !~~ NOT LIKE 'bruce' !~~ '%al%' ~ Match (regex), case sensitive 'thomas' ~ '*.thomas*.' ~* Match (regex), case insensitive 'thomas' ~* '*.Thomas*.' !~ Does not match (regex), case sensitive 'thomas' !~ '*.Thomas*.' !~* Does not match (regex), case insensitive 'thomas' !~ '*.vadim*.' Operators Operator Description Usage ! Factorial 3 ! !! Factorial (left operator) !! 3 % Modulo 5 % 4 % Truncate % 4.5 * Multiplication 2 * 3 + Addition 2 + 3 - Subtraction 2 - 3 / Division 4 / 2 : Natural Exponentiation : 3.0 ; Natural Logarithm (; 5.0) @ Absolute value @ -5.0 ^ Exponentiation 2.0 ^ 3.0 |/ Square root |/ 25.0 ||/ Cube root ||/ 27.0 Operators Operator Description Usage + Translation '((0,0),(1,1))'::box + '(2.0,0)'::point - Translation '((0,0),(1,1))'::box - '(2.0,0)'::point * Scaling/rotation '((0,0),(1,1))'::box * '(2.0,0)'::point / Scaling/rotation '((0,0),(2,2))'::box / '(2.0,0)'::point # Intersection '((1,-1),(-1,1))' # '((1,1),(-1,-1))' # Number of points in polygon # '((1,0),(0,1),(-1,0))' ## Point of closest proximity '(0,0)'::point ## '((2,0),(0,2))'::lseg && Overlaps? '((0,0),(1,1))'::box && '((0,0),(2,2))'::box &< Overlaps to left? '((0,0),(1,1))'::box &< '((0,0),(2,2))'::box &> Overlaps to right? '((0,0),(3,3))'::box &> '((0,0),(2,2))'::box <-> Distance between '((0,0),1)'::circle <-> '((5,0),1)'::circle << Left of? '((0,0),1)'::circle << '((5,0),1)'::circle <^ Is below? '((0,0),1)'::circle <^ '((0,5),1)'::circle >> Is right of? '((5,0),1)'::circle >> '((0,0),1)'::circle >^ Is above? '((0,5),1)'::circle >^ '((0,0),1)'::circle ?# Intersects or overlaps '((-1,0),(1,0))'::lseg ?# '((-2,-2),(2,2))'::box; ?- Is horizontal? '(1,0)'::point ?- '(0,0)'::point ?-| Is perpendicular? '((0,0),(0,1))'::lseg ?-| '((0,0),(1,0))'::lseg @-@ Length or circumference @-@ '((0,0),(1,0))'::path ?| Is vertical? '(0,1)'::point ?| '(0,0)'::point ?|| Is parallel? '((-1,0),(1,0))'::lseg ?|| '((-1,2),(1,2))'::lseg @ Contained or on '(1,1)'::point @ '((0,0),2)'::circle @@ Center of @@ '((0,0),10)'::circle ~= Same as '((0,0),(1,1))'::polygon ~= '((1,1),(0,0))'::polygon The time interval data type tinterval is a legacy from the original date/time types and is not as well supported as the more modern types. There are several operators for this type. Operators Operator Description Usage #< Interval less than? #<= Interval less than or equal to? #<> Interval not equal? #= Interval equal? #> Interval greater than? #>= Interval greater than or equal to? <#> Convert to time interval << Interval less than? | Start of interval ~= Same as <?> Time inside interval? Users may invoke operators using the operator name, as in: select * from emp where salary < 40000; Alternatively, users may call the functions that implement the operators directly. In this case, the query above would be expressed as: select * from emp where int4lt(salary, 40000); psql has a command (\dd) to show these operators.