PostgreSQL Expressions

An expression is composed of one or more values, operators, and PostgreSQL functions.

PostgreSQL expressions are similar to a formula and can be applied in query statements to find the result set that meets the specified conditions in the database.

Syntax

The syntax format of the SELECT statement is as follows:

SELECT column1, column2, columnN
FROM table_name
WHERE [CONDITION | EXPRESSION];

PostgreSQL expressions can have different types, which we will discuss next.

Boolean expressions

Boolean expressions are used to read data based on a specified condition:

SELECT column1, column2, columnN
FROM table_name
WHERE SINGLE VALUE MATCHTING EXPRESSION;

COMPANY.SQL file content 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 NOT NULL,
　　　NAME TEXT NOT NULL,
　　　AGE INT NOT NULL,
　　　ADDRESS CHAR(50),
　　　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 VALUES (7, 'James',　24, 'Houston',　10000.00 );

Create the COMPANY table, the data content is as follows:

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)

The following uses boolean expressions (SALARY=10000) to query data:

w3codeboxdb=# SELECT　*　FROM COMPANY WHERE SALARY =　10000;
　id | name | age | address | salary
----+-------+-----+----------+--------
　　7　| James |　　24　| Houston |　　10000
(1　row)

Numerical expression

Numerical expressions are often used in mathematical operations in query statements:

SELECT numerical_expression as OPERATION_NAME
[FROM table_name WHERE CONDITION] ;

numerical_expression It is a mathematical operation expression, for example:

w3codeboxdb=# SELECT (17　+　6) AS ADDITION ;
　addition　
----------
　　　　　　　23
(1　row)

In addition, PostgreSQL also has some built-in mathematical functions, such as:

• avg() : Returns the average value of an expression

• sum() : Returns the sum of the specified field

• count() : Returns the total number of records queried

The following example queries the total number of records in the COMPANY table:

w3codeboxdb=# SELECT COUNT(*) AS "RECORDS" FROM COMPANY;
　RECORDS
---------
　　　　　　　7
(1　row)

Date expression

The date expression returns the current date and time of the system, which can be used in various data operations. The following example query the current time:

w3codeboxdb=# SELECT CURRENT_TIMESTAMP;
　　　　　　　current_timestamp　　　　　　　
-------------------------------
　2019-06-13　10:49:06.419243+08
(1　row)