Expressions in the select-list are called value expressions or scalar expressions. This is because each expression in the select-list always returns only one value (but the value can be an array).
Scalar expressions can also be called SQL expressions or simply expressions. Each expression in SQL has its data type. It is determined by the data type(s) of the input. In many cases, it is possible to explicitly change the type of the data. Each item of the select-list becomes a column in the output dataset of a type that the corresponding expression has.
SQL expressions can contain the following:
- Column names (most of the cases)
- Constants
- Operator invocations
- Parentheses to control operations, precedence
- Function calls
- Aggregate expressions (we will discuss them later)
- Scalar subqueries
- Type casts
- Conditional expressions
This list is not complete. There are several other cases of using SQL expressions that are not covered by this chapter.
Column names can be qualified and unqualified. Qualified means that the name of the column is preceded by the table name and optionally the schema name, all separated by the period . symbol. Unqualified are just names of the fields without table references. Qualified column names must be used when several tables in the FROM clause have columns with the same name. Unqualified naming in this case will cause an error, column reference is ambiguous. This means that the database cannot understand which column is meant there. It is possible to use a table alias instead of a table name, and in case of using subqueries or functions, the alias must be used.
An example of using qualified names in a select-list is as follows:
SELECT car.car_id, car.number_of_owners FROM car_portal_app.car;
SQL supports all common operators as most of the other programming languages: logical, arithmetic, string, binary, date/time, and so on. We will discuss logical operators later in reference to SQL conditions. An example of using arithmetic operators in expressions would be as follows:
car_portal=> SELECT 1+1 AS two, 13%4 AS one, -5 AS minus_five, 5! AS factorial, |/25 AS square_root;
two | one | minus_five | factorial | square_root
-----+-----+------------+-----------+-------------
2 | 1 | -5 | 120 | 5
In PostgreSQL, it is also possible to create user-defined operators.
Function calls can also be a part of a SQL expression. To call a SQL function, one should use its name and the arguments in parenthesis:
car_portal=> SELECT substring('this is a string constant',11,6);
substring
-----------
string
A built-in function named substring was executed here. Three arguments were passed to the function: a string and two integers. This function extracts a part from the given string starting from the character specified by the second argument and having a specified length. By default, PostgreSQL assigns to the output column the same name as the function.
If a function has no arguments, it is still necessary to use parenthesis to indicate that it is a function name and not a field name or another identifier or a keyword.
Another thing that makes SQL very flexible and powerful is scalar subqueries, which can be used as a part of the value expression. It allows the developer to combine the results of different queries together. Scalar subqueries or scalar queries are queries that return exactly one column and one or zero records. They have no special syntax and their difference from non-scalar queries is nominal.
Consider the following example:
car_portal=> SELECT (SELECT 1) + (SELECT 2) AS three;
three
----------
3
Here, the result of one scalar query that returns the value of 1 is added to the result of another scalar query returning 2. The result of the whole expression is 3.
Type casting means changing the data type of a value. Type casts have several syntax patterns with the same meaning:
- CAST ( <value> AS <type>)
- <value>::<type>
- <type> '<value>'
- <type> (<value>)
The first is a common SQL syntax that is supported in most databases. The second is PostgreSQL specific. The third is only applicable for string constants and is usually used to define constants of other types but string or numeric. The last is function-like and can be applied only for types whose names are also existing function names, which is not very convenient. That's why this syntax is not widely used.
In many cases, PostgreSQL can do implicit type conversion. For example, the concatenation operator || (double vertical bar) takes two operands of type string. If one tries to concatenate a string with a number, PostgreSQL will convert the number to a string automatically:
car_portal=> SELECT 'One plus one equals ' || (1+1) AS str;
str
-----------------------
One plus one equals 2
A conditional expression is an expression returning different results depending on some condition. It is similar to an IF - THEN - ELSE statement in other programming languages. The syntax is as follows:
CASE WHEN <condition1> THEN <expression1> [WHEN <condition2> THEN <expression2> ...] [ELSE <expression n>] END
The behavior is understandable from the syntax: if the first condition is met, then the result of the first expression is returned; if the second condition is met, then the second expression is used; and so on. If no condition is met, then the expression specified in the ELSE part is evaluated. Each <condition> is itself an expression returning Boolean (true or false) result. All expressions used after the THEN keyword should return a result of the same type or at least of compatible types.
The number of condition-expression pairs should be one or more. ELSE is optional and when ELSE is not specified and no condition's result is true, then the whole CASE expression returns NULL.
CASE can be used in any place where the SQL expression is used. CASE expressions can be nested, that is they can be put one inside another as both condition part or expression part. The order of evaluating conditions is the same as specified in the expression. This means for any condition, it is known that all preceding conditions are evaluated as false. If any condition returns true, subsequent conditions are not evaluated at all.
There is a simplified syntax for CASE expressions. When all the conditions implement checking of equality of the same expression to several values, it is possible to use it like this:
CASE <checked_expression> WHEN <value1> THEN <result1> [WHEN <value2> THEN <result2> ...] [ELSE <result_n>] END
This means that when the value of checked_expression is equal to value1, the result1 is returned, and so on.
This is an example of using a CASE expression:
car_portal=> SELECT CASE WHEN now() > date_trunc('day', now()) + interval '12 hours'
THEN 'PM' ELSE 'AM' END;
case
------
PM
Here, the current time is compared to midday (the current time is truncated to day, which gives midnight, and then time interval of 12 hours is added). When the current time is after (operator >) midday, the expression returns the string PM, otherwise it returns AM.
A single SQL expression can have many operators, functions, type casts, and so on. The length of a SQL expression has no limits in language specification. The select-list is not the only place where SQL expressions can be used. In fact, they are used almost everywhere in SQL statements. For example, one can order the results of the query based on some SQL expression, as a sorting key. In an INSERT statement, they are used to calculate values of the fields for newly inserted records. SQL expressions that return Boolean values are often used as conditions in the WHERE clause.
PostgreSQL supports the short-circuit evaluation of the expressions and sometimes it skips the evaluation of some parts of the expression when they do not affect the result. For example, when evaluating the expression false AND z(), PostgreSQL will not call the z() function because the result of the AND operator is determined by its first operand, constant false, and it is false always, regardless of what the z() function would return.