Arithmetic Expressions

Objectives

By the end of this lesson, you should be able to:

• Use +, -, *, /, and % in a SELECT list
• Explain the difference between integer and numeric division
• Combine arithmetic with column values to compute new values

💡 Why this matters: A query rarely needs to return columns exactly as stored. “Salary after a 10% raise” or “price including tax” are computed values, PostgreSQL can compute them directly in the query instead of pulling raw numbers out and doing the math elsewhere.

⚠️ A note on verification: every statement and result in this lesson was run against a real, live PostgreSQL 18 database, using an employees table shaped like the one from Module 6 (id, first_name, last_name, email, department, salary, hire_date).

Arithmetic in a SELECT List

Arithmetic operators work directly on columns, right inside the column list:

SELECT first_name, salary, salary * 1.10 AS new_salary
FROM employees
WHERE first_name = 'Erin';

 first_name |  salary  | new_salary
------------+----------+------------
 Erin       | 74000.00 | 81400.0000

salary * 1.10 computes a 10% raise without changing anything stored in the table, it’s calculated fresh every time the query runs. The result column needs an alias (AS new_salary), otherwise PostgreSQL names it something unhelpful like ?column?.

Integer Division vs Numeric Division

SELECT 7 / 2 AS int_div;

 int_div
---------
       3

SELECT 7.0 / 2 AS num_div;

      num_div
--------------------
 3.5000000000000000

When both operands are integers, / performs integer division, the result is truncated toward zero, 7 / 2 is 3, not 3.5. As soon as either operand is a decimal (7.0 instead of 7), PostgreSQL performs true division and keeps the fractional part. This matters most when dividing an integer column by an integer literal, salary / 12 is safe here since salary is NUMERIC, but two integer columns divided together can silently truncate.

The % Modulo Operator

SELECT 7 % 2 AS remainder;

 remainder
-----------
         1

% returns the remainder of integer division. 7 % 2 is 1, because 7 divided by 2 is 3 with 1 left over. Common uses include checking even/odd (value % 2 = 0) or cycling through a fixed number of buckets.

Combining Arithmetic with Columns

SELECT first_name, salary, ROUND(salary / 12, 2) AS monthly
FROM employees
WHERE first_name = 'Jordan';

 first_name |  salary  | monthly
------------+----------+---------
 Jordan     | 61000.00 | 5083.33

salary / 12 estimates a monthly amount from an annual salary, wrapped in ROUND(..., 2) (covered in the next lesson) to keep it at two decimal places. Expressions like this can go anywhere a column can: the SELECT list, a WHERE condition, or an ORDER BY clause.

Try It

1. Write a query that shows every employee’s first_name and their salary after a 5% raise, aliased as new_salary.
2. Write a query for 10 / 3 and explain, before running it, whether the result will be truncated.
3. Write a query for 10.0 / 3 and compare it to the previous result.
4. Write a query that shows each employee’s first_name and their salary divided by 26 (biweekly pay periods), rounded to 2 decimal places.

Recap

• Arithmetic operators (+, -, *, /, %) work directly on columns and literals inside a query.
• Integer divided by integer truncates, at least one operand needs to be a decimal for a fractional result.
• % returns the remainder of integer division.
• Computed columns need an alias to be readable in the result.

Next lesson: string functions, manipulating and formatting text values.