PostgreSQL - Expressions

An expression in SQL is a combination of one or more values, operators, and functions that evaluates to a single value. Expressions are the building blocks of queries, allowing you to perform calculations, manipulate data, and specify complex conditions.

Syntax in a Query

Expressions can be used in various parts of a SQL statement, most commonly in the SELECT list and the WHERE clause.

SELECT expression_1, expression_2, ...
FROM table_name
WHERE conditional_expression;

Let’s explore the different types of PostgreSQL expressions with modern examples. We’ll use the following sample table:

CREATE TABLE employees (
    id INT PRIMARY KEY,
    first_name TEXT,
    last_name TEXT,
    department TEXT,
    salary NUMERIC(10, 2),
    hire_date DATE
);

INSERT INTO employees VALUES
(1, 'Alice', 'Williams', 'Engineering', 90000.00, '2022-03-15'),
(2, 'Bob', 'Smith', 'Engineering', 85000.00, '2021-07-01'),
(3, 'Charlie', 'Brown', 'Marketing', 72000.00, '2023-01-20'),
(4, 'Diana', 'Jones', 'Sales', 95000.00, '2020-11-10');

Boolean Expressions

Boolean expressions evaluate to TRUE, FALSE, or NULL. They are the foundation of the WHERE clause, used to filter rows based on specific conditions.

-- Find all employees in the 'Engineering' department with a salary over 80000
SELECT first_name, last_name, salary
FROM employees
WHERE department = 'Engineering' AND salary > 80000.00;

This query will produce the following result:

 first_name | last_name |  salary
------------+-----------+----------
 Alice      | Williams  | 90000.00
 Bob        | Smith     | 85000.00
(2 rows)

Numeric Expressions

Numeric expressions perform mathematical calculations. They can be used in the SELECT list to compute new values or in the WHERE clause for conditional logic.

-- Calculate a 5% bonus for each employee
SELECT first_name, salary, salary * 0.05 AS bonus
FROM employees;

This will produce:

 first_name |  salary  |  bonus
------------+----------+----------
 Alice      | 90000.00 | 4500.000
 Bob        | 85000.00 | 4250.000
 Charlie    | 72000.00 | 3600.000
 Diana      | 95000.00 | 4750.000
(4 rows)

Aggregate functions like SUM(), AVG(), COUNT() also produce a single numeric value from a set of rows.

-- Get the total number of employees and the average salary
SELECT COUNT(*) AS total_employees, AVG(salary) AS average_salary
FROM employees;

This will produce:

 total_employees |    average_salary
-----------------+-----------------------
               4 | 85500.00000000000000
(1 row)

Date and Time Expressions

These expressions are used to manipulate date and time values. PostgreSQL offers a rich set of functions and operators for date arithmetic.

-- Get the current server timestamp and find employees hired within the last 2 years
SELECT first_name, hire_date
FROM employees
WHERE hire_date > (CURRENT_DATE - INTERVAL '2 years');

Assuming the current date is in early 2024, this would produce:

 first_name | hire_date
------------+------------
 Alice      | 2022-03-15
 Charlie    | 2023-01-20
(2 rows)

You can also extract parts of a date or format it as a string.

-- Extract the year of hire and format the hire date
SELECT first_name,
       EXTRACT(YEAR FROM hire_date) AS hire_year,
       TO_CHAR(hire_date, 'Month DD, YYYY') AS formatted_date
FROM employees;