Easy Tutorial
Postgresql Like Postgresql Syntax Postgresql With Postgresql Null Postgresql Lock Postgresql View Postgresql Privileges Postgresql Limit Postgresql And Or Clauses Postgresql Autoincrement Postgresql Expressions Postgresql Data Type Postgresql Constraints Postgresql Where Clause Postgresql Drop Database Postgresql Select Database Windows Install Postgresql Postgresql Select Linux Install Postgresql Postgresql Join Postgresql Index Postgresql Tutorial Postgresql Schema Postgresql Trigger Postgresql Delete Postgresql Update Postgresql Alias Postgresql Union Postgresql Alter Table Postgresql Functions Postgresql Truncate Table Postgresql Group By Postgresql Sub Queries Postgresql Having Postgresql Insert Into Postgresql Create Table Postgresql Order By Postgresql Datetime Postgresql Create Database Postgresql Drop Table Postgresql Operators Postgresql Transaction Postgresql Distinct Mac Install Postgresql
❮ Postgresql Drop Table Postgresql Transaction ❯

PostgreSQL Operators

An operator is a symbol that tells the compiler to perform specific mathematical or logical manipulations.

PostgreSQL operators are reserved keywords or characters, typically used in the WHERE clause as filtering conditions.

Common operators include:

Arithmetic Operators

Assuming variable a is 2 and variable b is 3, then:

Operator Description Example
+ Addition a + b results in 5
- Subtraction a - b results in -1
* Multiplication a * b results in 6
/ Division b / a results in 1
% Modulus (remainder) b % a results in 1
^ Exponentiation a ^ b results in 8
/ Square root / 25.0 results in 5
/ Cube root / 27.0 results in 3
! Factorial 5 ! results in 120
!! Factorial (prefix operator) !! 5 results in 120

Example

tutorialprodb=# select 2+3;
 ?column?
----------
        5
(1 row)

tutorialprodb=# select 2*3;
 ?column?
----------
        6
(1 row)

tutorialprodb=# select 10/5;
 ?column?
----------
        2
(1 row)

tutorialprodb=# select 12%5;
 ?column?
----------
        2
(1 row)

tutorialprodb=# select 2^3;
 ?column?
----------
        8
(1 row)

tutorialprodb=# select |/ 25.0;
 ?column?
----------
        5
(1 row)

tutorialprodb=# select ||/ 27.0;
 ?column?
----------
        3
(1 row)

tutorialprodb=# select 5 !;
 ?column?
----------
      120
(1 row)

tutorialprodb=# select !!5;
 ?column?
----------
      120
(1 row)

Comparison Operators

Assuming variable a is 10 and variable b is 20, then:

Operator Description Example
= Equal to (a = b) is false.
!= Not equal to (a != b) is true.
<> Not equal to (a <> b) is true.
> Greater than (a > b) is false.
< Less than (a < b) is true.
>= Greater than or equal to (a >= b) is false.
<= Less than or equal to (a <= b) is true.

Example

Create a COMPANY table (download COMPANY SQL file), with the following data content:

tutorialprodb=# select * from COMPANY;
 id | name  | age | address   | salary
----+-------+-----+-----------+--------
  1 | Paul  |  32 | California|  20000
  2 | Allen |  25 | Texas     |  15000
  3 | Teddy |  23 | Norway    |  20000
  4 | Mark  |  25 | Rich-Mond |  65000
  5 | David |  27 | Texas     |  85000
  6 | Kim   |  22 | South-Hall|  45000
  7 | James |  24 | Houston   |  10000
(7 rows)

Retrieve data where the SALARY field is greater than 50000:

tutorialprodb=# SELECT * FROM COMPANY WHERE SALARY > 50000;
 id | name  | age |address    | salary
----+-------+-----+-----------+--------

4 | Mark  |  25 | Rich-Mond |  65000
5 | David |  27 | Texas     |  85000
(2 rows)

Reading data where the SALARY field is equal to 20000:

tutorialprodb=# SELECT * FROM COMPANY WHERE SALARY = 20000;
 id | name  | age |  address    | salary
----+-------+-----+-------------+--------
  1 | Paul  |  32 | California  |  20000
  3 | Teddy |  23 | Norway      |  20000
(2 rows)

Reading data where the SALARY field is not equal to 20000:

tutorialprodb=# SELECT * FROM COMPANY WHERE SALARY != 20000;
 id | name  | age |  address    | salary
----+-------+-----+-------------+--------
  2 | Allen |  25 | Texas       |  15000
  4 | Mark  |  25 | Rich-Mond   |  65000
  5 | David |  27 | Texas       |  85000
  6 | Kim   |  22 | South-Hall  |  45000
  7 | James |  24 | Houston     |  10000
(5 rows)

tutorialprodb=# SELECT * FROM COMPANY WHERE SALARY <> 20000;
 id | name  | age | address    | salary
----+-------+-----+------------+--------
  2 | Allen |  25 | Texas      |  15000
  4 | Mark  |  25 | Rich-Mond  |  65000
  5 | David |  27 | Texas      |  85000
  6 | Kim   |  22 | South-Hall |  45000
  7 | James |  24 | Houston    |  10000
(5 rows)

Reading data where the SALARY field is greater than or equal to 65000:

tutorialprodb=# SELECT * FROM COMPANY WHERE SALARY >= 65000;
 id | name  | age |  address  | salary
----+-------+-----+-----------+--------
  4 | Mark  |  25 | Rich-Mond |  65000
  5 | David |  27 | Texas     |  85000
(2 rows)

Logical Operators

PostgreSQL has the following logical operators:

No. Operator & Description
1 AND Logical AND operator. If both operands are non-zero, the condition becomes true. The WHERE statement in PostgresSQL can include multiple filter conditions using AND.
2 NOT Logical NOT operator. Used to reverse the logical state of its operand. If a condition is true, the Logical NOT operator will make it false. PostgresSQL has NOT EXISTS, NOT BETWEEN, NOT IN, etc.
3 OR Logical OR operator. If any of the two operands is non-zero, the condition becomes true. The WHERE statement in PostgresSQL can include multiple filter conditions using OR.

SQL uses a three-valued logic system, including true, false, and null, where null represents "unknown".

a b a AND b a OR b
TRUE TRUE TRUE TRUE
TRUE FALSE FALSE TRUE
TRUE NULL NULL TRUE
FALSE FALSE FALSE FALSE
FALSE NULL FALSE NULL
NULL NULL NULL NULL
a NOT a
TRUE FALSE
FALSE TRUE

Example

Create the COMPANY table (download COMPANY SQL file), with the following data content:

tutorialprodb=# select * from COMPANY;
 id | name  | age | address   | salary
----+-------+-----+-----------+--------
  1 | Paul  |  32 | California|  20000
  2 | Allen |  25 | Texas     |  15000
  3 | Teddy |  23 | Norway    |  20000
  4 | Mark  |  25 | Rich-Mond |  65000
  5 | David |  27 | Texas     |  85000
  6 | Kim   |  22 | South-Hall|  45000
  7 | James |  24 | Houston   |  10000
(7 rows)

Retrieve data where the AGE field is greater than or equal to 25 and the SALARY field is greater than or equal to 6500:

tutorialprodb=# SELECT * FROM COMPANY WHERE AGE >= 25 AND SALARY >= 6500;
 id | name  | age |                      address                  | salary
----+-------+-----+-----------------------------------------------+--------
  1 | Paul  |  32 | California                                    |  20000
  2 | Allen |  25 | Texas                                         |  15000
  4 | Mark  |  25 | Rich-Mond                                     |  65000
  5 | David |  27 | Texas                                         |  85000
(4 rows)

Retrieve data where the AGE field is greater than or equal to 25 or the SALARY field is greater than 6500:

tutorialprodb=# SELECT * FROM COMPANY WHERE AGE >= 25 OR SALARY >= 6500;
 id | name  | age |  address    | salary
----+-------+-----+-------------+--------
  1 | Paul  |  32 | California  |  20000
  2 | Allen |  25 | Texas       |  15000
  3 | Teddy |  23 | Norway      |  20000
  4 | Mark  |  25 | Rich-Mond   |  65000
  5 | David |  27 | Texas       |  85000
  6 | Kim   |  22 | South-Hall  |  45000
  7 | James |  24 | Houston     |  10000
  8 | Paul  |  24 | Houston     |  20000
  9 | James |  44 | Norway      |   5000
 10 | James |  45 | Texas       |   5000
(10 rows)

Retrieve data where the SALARY field is not NULL:

tutorialprodb=#  SELECT * FROM COMPANY WHERE SALARY IS NOT NULL;
 id | name  | age |  address    | salary
----+-------+-----+-------------+--------
  1 | Paul  |  32 | California  |  20000
  2 | Allen |  25 | Texas       |  15000
  3 | Teddy |  23 | Norway      |  20000
4 | Mark  |  25 | Rich-Mond   |  65000
  5 | David |  27 | Texas       |  85000
  6 | Kim   |  22 | South-Hall  |  45000
  7 | James |  24 | Houston     |  10000
  8 | Paul  |  24 | Houston     |  20000
  9 | James |  44 | Norway      |   5000
 10 | James |  45 | Texas       |   5000
(10 rows)

---

## Bitwise Operators

Bitwise operators work on bits and perform bit-by-bit operations. The truth tables for &, |, and ^ are as follows:

| p | q | p & q | p | q |
| --- | --- | --- | --- |
| 0 | 0 | 0 | 0 |
| 0 | 1 | 0 | 1 |
| 1 | 1 | 1 | 1 |
| 1 | 0 | 0 | 1 |

Suppose 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^B = 0011 0001

~A  = 1100 0011

The following table lists the bitwise operators supported by PostgreSQL. Assume variable **A** holds 60 and variable **B** holds 13, then:

| 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 XOR Operator copies the bit if it is set in one operand but not both. | (A # B) will give 49, which is 0011 0001 |
| ~ | 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 |

### Example

tutorialprodb=# select 60 | 13;

?column?

   61

(1 row)

tutorialprodb=# select 60 & 13;

?column?

   12

(1 row)

tutorialprodb=# select (~60);

?column?

  -61

(1 row)

tutorialprodb=# select (60 << 2);

?column?

  240

(1 row)

tutorialprodb=# select (60 >> 2);

?column?

   15

(1 row)

tutorialprodb=# select 60 # 13;

?column?

   49

(1 row) ```

❮ Postgresql Drop Table Postgresql Transaction ❯