- PostgreSQL - Home
- PostgreSQL - Overview
- PostgreSQL - Environment Setup
- PostgreSQL - Syntax
- PostgreSQL - Data Types
- PostgreSQL - Operators
- PostgreSQL - Expressions
- PostgreSQL Database
- PostgreSQL - Create Database
- PostgreSQL - ALTER DATABASE
- PostgreSQL - Drop Database
- PostgreSQL - Loading Database
- PostgreSQL - Rename Database
- PostgreSQL - Select Database
- PostgreSQL - Show Database
- PostgreSQL Query Operations
- PostgreSQL - SELECT
- PostgreSQL - CREATE
- PostgreSQL - INSERT
- PostgreSQL - UPDATE
- PostgreSQL - DELETE
- PostgreSQL - ALTER TABLE Command
- PostgreSQL - WHERE Clause
- PostgreSQL - ORDER BY Clause
- PostgreSQL - GROUP BY
- PostgreSQL - HAVING Clause
- PostgreSQL - DISTINCT Keyword
- PostgreSQL - LIMIT Clause
- PostgreSQL - LIKE Clause
- PostgreSQL - WITH Clause
- PostgreSQL - AND & OR Clauses
- PostgreSQL - DROP TABLE
- PostgreSQL - Upsert
- TRUNCATE TABLE Command
- PostgreSQL JOINS & Schemas
- PostgreSQL Schemas
- PostgreSQL Joins
- PostgreSQL Data Integrity & Transaction
- PostgreSQL - Constraints
- PostgreSQL - Transactions
- PostgreSQL - Commit
- PostgreSQL - Rollback
- PostgreSQL - Views
- PostgreSQL Functions
- PostgreSQL - ALIAS Syntax
- PostgreSQL - Functions
- PostgreSQL - Useful Function
- PostgreSQL - MAX() Function
- PostgreSQL - MIN() Function
- PostgreSQL - SUM() Function
- PostgreSQL - COUNT() Function
- PostgreSQL - Array Function
- PostgreSQL - String Function
- PostgreSQL - Numeric Function
- PostgreSQL Operators
- PostgreSQL - UNION Operator
- PostgreSQL - INTERSECT Operator
- PostgreSQL - EXCEPT Operator
- PostgreSQL - ANY Operator
- PostgreSQL - ALL Operator
- PostgreSQL - EXISTS Operator
- PostgreSQL Interface
- PostgreSQL - C / C++
- PostgreSQL - Java
- PostgreSQL - PHP
- PostgreSQL - Perl
- PostgreSQL - Python
- Advanced PostgreSQL
- PostgreSQL - NULL Values
- PostgreSQL - Triggers
- PostgreSQL - Indexes
- PostgreSQL - Locks
- PostgreSQL - Sub Queries
- PostgreSQL - Auto Increment
- PostgreSQL - Privileges
- PostgreSQL - Date/Time Functions & Operators
- PostgreSQL - Errors & Messages
- PostgreSQL - Assert
PostgreSQL - Operators
What is an Operator in PostgreSQL?
An operator is a reserved word or a character used primarily in a PostgreSQL statement's WHERE clause to perform operation(s), such as comparisons and arithmetic operations.
Operators are used to specify conditions in a PostgreSQL statement and to serve as conjunctions for multiple conditions in a statement.
- Arithmetic operators
- Comparison operators
- Logical operators
- Bitwise operators
PostgreSQL Arithmetic Operators
Assume variable a holds 2 and variable b holds 3, then −
| Operator | Description | Example |
|---|---|---|
| + | Addition - Adds values on either side of the operator | a + b will give 5 |
| - | Subtraction - Subtracts right hand operand from left hand operand | a - b will give -1 |
| * | Multiplication - Multiplies values on either side of the operator | a * b will give 6 |
| / | Division - Divides left hand operand by right hand operand | b / a will give 1 |
| % | Modulus - Divides left hand operand by right hand operand and returns remainder | b % a will give 1 |
| ^ | Exponentiation - This gives the exponent value of the right hand operand | a ^ b will give 8 |
| |/ | square root | |/ 25.0 will give 5 |
| ||/ | Cube root | ||/ 27.0 will give 3 |
| ! | factorial | 5 ! will give 120 |
| !! | factorial (prefix operator) | !! 5 will give 120 |
PostgreSQL Comparison Operators
Assume variable a holds 10 and variable b holds 20, then −
| Operator | Description | Example |
|---|---|---|
| = | Checks if the values of two operands are equal or not, if yes then condition becomes true. | (a = b) is not true. |
| != | Checks if the values of two operands are equal or not, if values are not equal then condition becomes true. | (a != b) is true. |
| <> | Checks if the values of two operands are equal or not, if values are not equal then condition becomes true. | (a <> b) is true. |
| > | Checks if the value of left operand is greater than the value of right operand, if yes then condition becomes true. | (a > b) is not true. |
| < | Checks if the value of left operand is less than the value of right operand, if yes then condition becomes true. | (a < b) is true. |
| >= | Checks if the value of left operand is greater than or equal to the value of right operand, if yes then condition becomes true. | (a >= b) is not true. |
| <= | Checks if the value of left operand is less than or equal to the value of right operand, if yes then condition becomes true. | (a <= b) is true. |
PostgreSQL Logical Operators
Here is a list of all the logical operators available in PostgresSQL.
| S. No. | Operator & Description |
|---|---|
| 1 |
AND The AND operator allows the existence of multiple conditions in a PostgresSQL statement's WHERE clause. |
| 2 |
NOT The NOT operator reverses the meaning of the logical operator with which it is used. Eg. NOT EXISTS, NOT BETWEEN, NOT IN etc. This is negate operator. |
| 3 |
OR The OR operator is used to combine multiple conditions in a PostgresSQL statement's WHERE clause. |
PostgreSQL Bit String Operators
Bitwise operator works on bits and performs bit-by-bit operation. The truth table for & and | is as follows −
| p | q | p & q | p | q |
|---|---|---|---|
| 0 | 0 | 0 | 0 |
| 0 | 1 | 0 | 1 |
| 1 | 1 | 1 | 1 |
| 1 | 0 | 0 | 1 |
Assume if A = 60; and B = 13; now in binary format they will be as follows −
A = 0011 1100
B = 0000 1101
-----------------
A&B = 0000 1100
A|B = 0011 1101
~A = 1100 0011
The Bitwise operators supported by PostgreSQL are listed in the following table −
| Operator | Description | Example |
|---|---|---|
| & | Binary AND Operator copies a bit to the result if it exists in both operands. | (A & B) will give 12 which is 0000 1100 |
| | | Binary OR Operator copies a bit if it exists in either operand. | (A | B) will give 61 which is 0011 1101 |
| ~ | Binary Ones Complement Operator is unary and has the effect of 'flipping' bits. | (~A ) will give -61 which is 1100 0011 in 2's complement form due to a signed binary number. |
| << | Binary Left Shift Operator. The left operands value is moved left by the number of bits specified by the right operand. | A << 2 will give 240 which is 1111 0000 |
| >> | Binary Right Shift Operator. The left operands value is moved right by the number of bits specified by the right operand. | A >> 2 will give 15 which is 0000 1111 |
| # | bitwise XOR. | A # B will give 49 which is 00110001 |