5.15. PostgreSQL operator

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

The PostgreSQL operator is a reserved keyword or character that is commonly used in WHERE statements as a filter condition.

Common operators are:

Arithmetic operator
Comparison operator
Logical operator
Bitwise operator

5.15.1. Arithmetic operator ¶

Assuming that variable an is 2 and variable b is 3, then:

Operator	Description	Example
`+`	Add	A + b result is 5
`-`	Minus	A-b result is-1
`*`	Multiply	A * b result is 6
`/`	Except	B / a result is 1
`%`	Module (remainder)	B a result is 1
`^`	Index	A ^ b result is 8
`\|/`	Square root	`\|/ 25.0` The result is 5.
`\|\|/`	cubic root	\|\|/ 27.0 结果为 3
`!`	Factorial	5! The result is 120
`!!`	Factorial (prefix operator)	!! 5 the result is 120

5.15.2. Example ¶

runoobdb=# select 2+3; ?column? ---------- 5 (1 row) runoobdb=# select 2*3; ?column? ---------- 6 (1 row) runoobdb=# select 10/5; ?column? ---------- 2 (1 row) runoobdb=# select 12%5; ?column? ---------- 2 (1 row) runoobdb=# select 2^3; ?column? ---------- 8 (1 row) runoobdb=# select |/ 25.0; ?column? ---------- 5 (1 row) runoobdb=# select ||/ 27.0; ?column? ---------- 3 (1 row) runoobdb=# select 5 !; ?column? ---------- 120 (1 row) runoobdb=# select !!5; ?column? ---------- 120 (1 row) 

5.15.3. Comparison operator ¶

Assuming that variable an 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.

5.15.4. Example ¶

Create the COMPANY table ( 下载 COMPANY SQL 文件 ), the data are as follows:

runoobdb=# 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) 

Read data with a SALARY field greater than 50000:

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

Read data with a SALARY field equal to 20000:

runoobdb=# 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 whose SALARY field is not equal to 20000:

runoobdb=# 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) runoobdb=# 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 with a SALARY field greater than or equal to 65000:

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

5.15.5. Logical operator ¶

There are several types of PostgreSQL logical operators:

Serial number

Operator & description

AND

Logic and operators. If both operands are nonzero, the condition is true.

The WHERE statement in PostgresSQL can contain multiple filter criteria with AND.

NOT

Logical non-operator. Used to reverse the logical state of operands. If the condition is true, the logical non-operator makes it false.

PostgresSQL has operators like NOT EXISTS, NOT BETWEEN, NOT IN, and so on.