English | 简体中文 | 繁體中文 | Русский язык | Français | Español | Português | Deutsch | 日本語 | 한국어 | Italiano | بالعربية

PostgreSQL Basic Tutorial

PostgreSQL Advanced Tutorial

PostgreSQL Interface

PostgreSQL Operators

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

PostgreSQL operators are reserved keywords or characters, generally used in WHERE clauses as filtering conditions.

Common operators include:

  • Arithmetic operators

  • Comparison operator

  • id | name | age | address | salary

  • Bitwise operators

Arithmetic operators

Assuming variable a is 2, variable b is 3，then：

OperatorDescriptionExample
+ Additiona + b The result is 5
-Subtractiona - b The result is -1
*Multiplicationa * b The result is 6
/Divisionb / a The result is 1
%Modulus (remainder)b % a The result is 1
^Exponentiationa ^ b The result is 8
|/Square root|/ 25.0 The result is 5
||/Cube root||/ 27.0 The result is 3
!Factorial5 ! The result is 120
!!Factorial (prefix operator)!! 5 The result is 120

Online example

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

Comparison operator

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

OperatorDescriptionExample
=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.

Online example

The content of COMPANY.SQL file is as follows:

--　This is the file to create COMPANY table and to populate it with　7　records.
--　Just copy and paste them on psql prompt.
DROP TABLE COMPANY;
CREATE TABLE COMPANY
　　　ID INT PRIMARY KEY
　　　NAME
　　　AGE
　　　ADDRESS50),
　　　SALARY REAL
);
INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (1, 'Paul',　32, 'California',　20000.00 );
INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (2, 'Allen',　25, 'Texas',　15000.00 );
INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (3, 'Teddy',　23, 'Norway',　20000.00 );
INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (4, 'Mark',　25, 'Rich-Mond ',　65000.00 );
INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (5, 'David',　27, 'Texas',　85000.00 );
INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (6, 'Kim',　22, 'South-Hall',　45000.00 );
INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)7, 'James',　24, 'Houston',　10000.00 );

Create the COMPANY table with the following data:

w3codeboxdb=# 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 the field SALARY that is greater than 5Read the SALARY field not equal to

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

Read the field SALARY that is equal to 2Read the SALARY field not equal to

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

FROM COMPANY WHERE SALARY = 2Read the SALARY field not equal to

w3codeboxdb=# SELECT　*　0000 of the data:　2FROM COMPANY WHERE SALARY <>
　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)
w3codeboxdb=# SELECT　*　FROM COMPANY WHERE SALARY !=　2FROM COMPANY WHERE SALARY <>
　0000;
----+-------+-----+------------+--------
　　2　| Allen |　　25　Mond　　15000
　　4　| Mark |　　25　|　Rich-id | name | age | address | salary　　65000
　　5　|　David　|　　27　Mond　　85000
　　6　|　Kim　　|　　22　|　South-| Texas　　45000
　　7　|　James　|　　24　Hall　　10000
(5　rows)

| Houston 　65Read the SALARY field greater than or equal to

w3codeboxdb=# SELECT　*　000 of the data:　65FROM COMPANY WHERE SALARY >=
　000;
----+-------+-----+-----------+--------
　　4　| Mark |　　25　|　Rich-Mond |　　65000
　　5　|　David　|　　27　| Texas |　　85000
(2　rows)

id | name | age | address | salary

Logical Operator

PostgreSQL logical operators include the following types:Serial Number
1

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

Operator & Description

Logical AND operator. If both operands are non-zero, the condition is true.



2

NOT

The WHERE clause in PostgresSQL can include multiple filtering conditions with AND.

Logical NOT operator. It reverses the logical state of the operand. If the condition is true, the logical NOT operator makes it false.
3

OR

PostgresSQL has NOT EXISTS, NOT BETWEEN, NOT IN, etc. operators.

Logical OR operator. If either of the operands is non-zero, the condition is true.

The WHERE clause in PostgresSQL can include multiple filtering conditions with OR.

aba SQL uses a three-valued logical system, including true, false, and null, where null represents 'unknown'. ba OR b
TRUETRUETRUETRUE
TRUEFALSEFALSETRUE
TRUENULLNULLTRUE
FALSEFALSEFALSEFALSE
FALSENULLFALSENULL
NULLNULLNULLNULL
aNOT a
TRUEFALSE
FALSETRUE
NULLNULL

Online example

COMPANY.SQL file content as follows:

--　This is the file to create COMPANY table and to populate it with　7　records.
--　Just copy and paste them on psql prompt.
DROP TABLE COMPANY;
CREATE TABLE COMPANY
　　　ID INT PRIMARY KEY
　　　NAME
　　　AGE
　　　ADDRESS50),
　　　SALARY REAL
);
INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (1, 'Paul',　32, 'California',　20000.00 );
INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (2, 'Allen',　25, 'Texas',　15000.00 );
INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (3, 'Teddy',　23, 'Norway',　20000.00 );
INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (4, 'Mark',　25, 'Rich-Mond ',　65000.00 );
INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (5, 'David',　27, 'Texas',　85000.00 );
INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (6, 'Kim',　22, 'South-Hall',　45000.00 );
INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)7, 'James',　24, 'Houston',　10000.00 );

Create the COMPANY table with the following data:

w3codeboxdb=# 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 where the AGE field is greater than or equal to 　25 and the SALARY field is greater than or equal to 6500 data:

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

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

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

Read data where the SALARY field is not NULL:

w3codeboxdb=# 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 operate on bits and perform operations bit by bit. The truth tables for &、 | and ^ are as follows:

pqp & qp | q
0000
0101
1111
1001

Assume if A = 60，and B = 13，now represented in binary format, 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 shows the bitwise operators supported by PostgreSQL. Assume the variable A The value of 60，variable B The value of 13，then：

OperatorDescriptionExample
&

Bitwise AND operation, performs a "AND" operation on binary bits. Operation rules:

0&0=0;　　　
0&1=0;　　　　
1&0=0;　　　　　
1&1=1;
(A & B) will get 12，which is 0000 1100
|

Bitwise OR operator, performs a "OR" operation on binary bits. Operation rules:

0|0=0;　　　
0|1=1;　　　
1|0=1;　　　　
1|1=1;
(A | B) will get 61，which is 0011 1101
#

XOR operator, performs a "XOR" operation on binary bits. Operation rules:

0#0=0;　　　
0#1=1;　　　
1#0=1;　　
1#1=0;
(A # B) will get 49，which is 0011 0001
~

Negation operator, performs a "negation" operation on binary bits. Operation rules:

~1=0;　　　
~0=1;
(~A ) will get -61，which is 1100 0011，which is the two's complement form of a signed binary number.
<<Binary left shift operator. Shifts all binary bits of an operand to the left by several bits (the leftmost bits are discarded, and 0 is filled on the right).A << 2 You will get 240，which is 1111 0000
>>Binary right shift operator. Shifts all binary bits of a number to the right by several bits, filling with 0 on the left for positive numbers and with the leftmost bit for negative numbers.1，discard the right side.A >> 2 You will get 15，which is 0000 1111

Online example

w3codeboxdb=# select　60　|　13;
　?column?
----------
　　　　　　　61
(1　row)
w3codeboxdb=# select　60　&　13;
　?column?
----------
　　　　　　　12
(1　row)
w3codeboxdb=# select (~60);
　?column?
----------
　　　　　　-61
(1　row)
w3codeboxdb=# select60　<<　2);
　?column?
----------
　　　　　　240
(1　row)
w3codeboxdb=# select60　>>　2);
　?column?
----------
　　　　　　　15
(1　row)
w3codeboxdb=# select　60　#　13;
　?column?
----------
　　　　　　　49
(1　row)
PostgreSQL ExpressionsPostgreSQL SELECT Statement