YouTip

An operator is a symbol that tells the compiler to perform specific mathematical or logical operations. PostgreSQL operators are reserved keywords or characters, generally used in WHERE statements as filter conditions. Common operators include: * Arithmetic Operators * Comparison Operators * Logical Operators * Bitwise Operators * * * ## 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 | | % | Modulo (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 | ### Examples tutorialdb=# select 2+3; ?column?---------- 5(1 row) tutorialdb=# select 2*3; ?column?---------- 6(1 row) tutorialdb=# select 10/5; ?column?---------- 2(1 row) tutorialdb=# select 12%5; ?column?---------- 2(1 row) tutorialdb=# select 2^3; ?column?---------- 8(1 row) tutorialdb=# select |/ 25.0; ?column?---------- 5(1 row) tutorialdb=# select ||/ 27.0; ?column?---------- 3(1 row) tutorialdb=# select 5 !; ?column?---------- 120(1 row) tutorialdb=# select !!5; ?column?---------- 120(1 row) * * * ## Comparison Operators Assuming variable a is 10, and variable b is 20, then: | Operator | Description | Example | | --- | --- | --- | | = | Equals | (a = b) is false. | | != | Not Equal | (a != b) is true. | | | Not Equal | (a b) is true. | | > | Greater Than | (a > b) is false. | | < | Less Than | (a = | Greater Than or Equal | (a >= b) is false. | | <= | Less Than or Equal | (a 50000; id | name | age |address | salary ----+-------+-----+-----------+-------- 4 | Mark | 25 | Rich-Mond | 65000 5 | David | 27 | Texas | 85000(2 rows) Read data where SALARY field equals 20000: tutorialdb=# SELECT * FROM COMPANY WHERE SALARY = 20000; id | name | age | address | salary ----+-------+-----+-------------+-------- 1 | Paul | 32 | California | 20000 3 | Teddy | 23 | Norway | 20000(2 rows) Read data where SALARY field does not equal 20000: tutorialdb=# 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) tutorialdb=# 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) Read data where SALARY field is greater than or equal to 65000: tutorialdb=# 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 is true. WHERE statements in PostgreSQL can use AND to include multiple filter conditions. | | 2 | **NOT** Logical NOT operator. Used to reverse the logical state of an operand. If the condition is true, the NOT operator makes it false. PostgreSQL has operators such as NOT EXISTS, NOT BETWEEN, NOT IN. | | 3 | **OR** Logical OR operator. If either of the two operands is non-zero, the condition is true. WHERE statements in PostgreSQL can use OR to include multiple filter conditions. | SQL uses a three-valued logic system, including true, false, and null, where null represents "unknown". | _`