YouTip

# PostgreSQL Expressions An expression is composed of one or more values, operators, and PostgreSQL functions. PostgreSQL expressions are similar to formulas that we can apply in query statements to find the result set for a specified condition in the database. ### Syntax The syntax format for a 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 Boolean expressions fetch data based on a specified condition: SELECT column1, column2, columnN FROM table_name WHERE SINGLE VALUE MATCHTING EXPRESSION; Create the COMPANY table ((https://static.jyshare.com/download/company.sql) ), with the following data: tutorialdb# 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: tutorialdb=# SELECT * FROM COMPANY WHERE SALARY = 10000; id | name | age | address | salary ----+-------+-----+----------+-------- 7 | James | 24 | Houston | 10000(1 row) ### Numeric Expressions Numeric expressions are commonly used for mathematical operations in query statements: SELECT numerical_expression as OPERATION_NAME ; **numerical_expression** is a mathematical expression, for example: tutorialdb=# SELECT (17 + 6) AS ADDITION ; addition ---------- 23(1 row) Additionally, PostgreSQL provides 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 a query The following example queries the total number of records in the COMPANY table: tutorialdb=# SELECT COUNT(*) AS "RECORDS" FROM COMPANY; RECORDS --------- 7(1 row) ### Date Expressions Date expressions return the current system date and time, which can be used for various data operations. The following example queries the current time: tutorialdb=# SELECT CURRENT_TIMESTAMP; current_timestamp ------------------------------- 2019-06-13 10:49:06.419243+08(1 row)