PostgreSQL Expressions

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

PostgreSQL expressions are like formulas that we can apply in query statements to find result sets that meet 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 be of different types, which we will discuss next.

Boolean Expressions

A boolean expression reads data based on a specified condition:

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

Create the COMPANY table (Download the COMPANY SQL file), and the data is as follows:

exampledb# 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 a boolean expression (SALARY=10000) to query data:

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

Numeric Expressions

Numeric expressions are often used for mathematical operations in query statements:

SELECT numerical_expression as  OPERATION_NAME
[FROM table_name WHERE CONDITION] ;

numerical_expressionis a mathematical operation expression, for example:

exampledb=# 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 a specified field
• count(): returns the total number of records in the query

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

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

Date Expressions

A date expression returns the current system date and time and can be used for various data operations. The following example queries the current time:

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