Support Operators

Operators are similar to functions (see Section  on page ). Table  lists the most common operators.

 

Table: Common operators

Type	Function	Example	Returns
Character	||	col1 || col2	append col2 on to the end of col1
String	~	col ~ pattern	BOOLEAN , col matches regular expression  pattern
	!~	col !~ pattern	BOOLEAN, col does not match regular expression pattern
	~*	col ~* pattern	same as ~, but case-insensitive
	!~*	col !~* pattern	same as !~, but case-insensitive
	~~	col ~~ pattern	BOOLEAN, col matches LIKE pattern
	LIKE	col LIKE pattern	same as ~~
	!~~	col !~~ pattern	BOOLEAN, col does not match LIKE pattern
	NOT LIKE	col NOT LIKE pattern	same as !~~
Number	!	!col	factorial
	+	col1 + col2	addition
	-	col1 - col2	subtraction
	*	col1 * col2	multiplication
	/	col1 / col2	division
	%	col1 % col2	remainder/modulo
	^	col1 ^ col2	col1 raised to the power of col2
Temporal	+	col1 + col2	addition of temporal values
	-	col1 - col2	subtraction of temporal values
	(...) OVERLAPS	(c1, c2) OVERLAPS	BOOLEAN indicating cols overlap in time
	(...)	(c3, c4)
Geometric			see psql's \do for a list of geometric operators
Network	<<	col1 << col2	BOOLEAN indicating if col1 is a subnet of col2
	<<=	col1 <<= col2	BOOLEAN indicating if col1 is equal or a subnet of col2
	>>	col1 >> col2	BOOLEAN indicating if col1 is a supernet of col2
	>>=	col1 >>= col2	BOOLEAN indicating if col1 is equal or a supernet of col2

Psql's \do command shows all defined operators and their arguments.

 All data types support the standard comparison operators <, <=, =, >=, >, and <>. Not all operator/type combinations are defined, however. For example, if you try to add two DATE values, you will get an error, as shown in the first query of Figure .    

        test=> SELECT CAST('1/1/1992' AS DATE) + CAST('1/1/1993' AS DATE);
        ERROR:  Unable to identify an operator '+' for types 'date' and 'date'
                You will have to retype this query using an explicit cast
        test=> SELECT CAST('1/1/1992' AS DATE) + CAST('1 year' AS INTERVAL);
                ?column?        
        ------------------------
         1993-01-01 00:00:00-05
        (1 row) 
         
        test=> SELECT CAST('1/1/1992' AS TIMESTAMP) + '1 year';
                ?column?        
        ------------------------
         1993-01-01 00:00:00-05
        (1 row)