PostgreSQL - Operators
Operators are used to perform operation on two operands. Operators in PostgreSQL can be categorized as follows:
- Arithmetic operators
- Comparison operators
- Logical operators
PostgreSQL Arithmetic operators
Arithmetic operators are used to perform arithmetic operations on two operands.
| Operator | Name | Description | Example |
|---|---|---|---|
| + | Addition | Add two values | More Info |
| - | Subtraction | Subtract one value from another | More Info |
| * | Multiplication | Multiply two values | More Info |
| / | Division | Divide one value by another | More Info |
| % | Modulo | Returns remainder of division operation | More Info |
PostgreSQL Comparison operators
Comparison operators are used to compare values of two operands. It returns true when values matches and false when values does not match.
| Operator | Description | Example |
|---|---|---|
| = | Equal to | More Info |
| != | Not equal to | More Info |
| <> | Not equal to | More Info |
| > | Greater than | More Info |
| < | Less than | More Info |
| >= | Greater than or equal to | More Info |
| <= | Less than or equal to | More Info |
PostgreSQL Logical operators
Logical operators are used to create and combine one or more conditions.
| Operator | Description |
|---|---|
| ALL | Returns true if all of the subquery values meet the condition. |
| AND | Only includes rows where both conditions is true. |
| ANY | Returns true if any of the subquery values meet the condition. |
| BETWEEN | Selects values within a given range. |
| EXISTS | Tests for the existence of records from a subquery. |
| ILIKE | Searches for a specified pattern in a column. Performs case-insensitive match. |
| IN | Allows you to specify multiple values in a WHERE clause. |
| LIKE | Searches for a specified pattern in a column. |
| NOT | Only includes rows where a condition is not true. |
| NOT ILIKE | Negation of ILIKE. |
| NOT LIKE | Negation of LIKE. |
| OR | Returns True when any of the conditions is true. |
| IS NULL | Tests for null values. |
| IS NOT NULL | Tests for non-null values. |
| SOME | Returns true if any of the subquery values meet the condition. |
PostgreSQL Operators Precedence
Operator precedence (order of operations) is a collection of rules that reflect conventions about which procedures to perform first in order to evaluate a given expression.
For example, multiplication has higher precedence than addition. Thus, the expression 1 + 2 × 3 is interpreted to have the value 1 + (2 × 3) = 7, and not (1 + 2) × 3 = 9. When exponent is used in the expression, it has precedence over both addition and multiplication. Thus 3 + 52 = 28 and 3 × 52 = 75.
The following table lists the precedence of PostgreSQL operators. Operators are listed top to bottom, in descending precedence. Operators with higher precedence are evaluated before operators with relatively lower precedence.
| Precedence | Operators | Associativity | Description |
|---|---|---|---|
| 20 | :: | left | PostgreSQL-style typecast |
| 19 | [ ] | left | array element selection |
| 18 | . | left | table/column name separator |
| 17 | - | right | unary minus |
| 16 | ^ | left | exponentiation |
| 15 | *, /, % | left | multiplication, division, modulo |
| 14 | +, - | left | addition, subtraction |
| 13 | IS | test for TRUE, FALSE, UNKNOWN, NULL | |
| 12 | ISNULL | test for NULL | |
| 11 | NOTNULL | test for NOT NULL | |
| 10 | (any other) | left | all other native and user-defined operators |
| 9 | IN | set membership | |
| 8 | BETWEEN | containment | |
| 7 | OVERLAPS | time interval overlap | |
| 6 | LIKE, ILIKE | string pattern matching | |
| 5 | <, > | less than, greater than | |
| 4 | = | right | equality, assignment |
| 3 | NOT | right | logical negation |
| 2 | AND | left | logical conjunction |
| 1 | OR | left | logical disjunction |
