Calculated columns — Retrieving and Filtering Data

Objective

By the end of this lesson you can add calculated columns to a query (arithmetic, rounding, text and dates) and predict the data type of the result, including the integer-division trap.

Why it matters: Reports rarely show raw columns only: stock value, discounted price, margin and days since signup are all calculated. Knowing how PostgreSQL calculates them (and when it silently drops decimals) is what keeps your numbers right.

Dataset: Starter shop. Examples run in the editor on the right.

So far every column in your results came straight from a table. But the SELECT list can hold expressions too: a calculation that PostgreSQL evaluates once for every row, producing a new column that exists only in the result. The stored data never changes.

An expression can combine columns, fixed values (called literals, like 0.85 or 'SAR'), operators and functions:

arithmetic: +, -, *, / and % (remainder)
functions such as ROUND(value, 2)
text joining with ||
date arithmetic, such as joined_on + 30

SELECT column_a,
       expression AS new_name
FROM table_name;

AS gives the calculated column a name. You'll study aliases properly in the next lesson; for now, always name calculated columns.

ExampleWhat is the stock worth?

SELECT product_name, price, stock,
       price * stock AS stock_value
FROM products;

For each of the 10 products, PostgreSQL multiplies that row's price by that row's stock. The Notebook A5 pack has 0 units in stock, so its stock_value is 0.00. Because price is numeric(12,2), the product keeps two decimals.

Data types decide the answer

Every value has a data type: integer for whole numbers, numeric for exact decimals (money), text, date and so on. When PostgreSQL calculates, the result's type follows the types of the inputs. That matters most for division:

integer / integer is integer division: the decimals are thrown away (not rounded). 7 / 2 is 3, and 1 / 4 is 0.
If either side is numeric (for example 2.0, or a numeric column such as price), you get an exact decimal: 7 / 2.0 is 3.5.
% gives the remainder of integer division: 7 % 2 is 1.

To force a decimal result from two integer columns, make one side numeric: multiply by 100.0 first, or cast it with column::numeric (PostgreSQL's short form of CAST(column AS numeric)).

ExampleInteger division in action

SELECT 7 / 2       AS integer_division,
       7 / 2.0     AS decimal_division,
       7 % 2       AS remainder,
       7::numeric / 2 AS cast_first,
       1 / 4 * 100 AS pct_wrong,
       1 * 100.0 / 4 AS pct_right;

A SELECT without FROM evaluates expressions once, which is a handy calculator. 7 / 2 returns 3 because both sides are integers; 7 / 2.0 and 7::numeric / 2 return 3.5. The last two columns show the classic percentage bug: 1 / 4 is already 0 before the multiplication, so pct_wrong is 0, while pct_right is 25.

Quick check

The products table stores stock as an integer. What does SELECT stock / 50 return for a product with 30 units?

Rounding

Decimal arithmetic can produce long tails of digits (75.50 * 0.85 is 64.1750). ROUND(value, 2) rounds to two decimal places, the usual choice for money. ROUND(value) rounds to a whole number. Round at the end of a calculation, not in the middle, or the rounding errors add up.

Text and dates are expressions too

|| joins text: 'Order #' || order_id produces Order #7. PostgreSQL converts the number to text for you when one side is already text. Functions like upper(city) and length(customer_name) work row by row as well. With dates, adding an integer adds days: joined_on + 30 is a date thirty days later, and subtracting two dates gives a number of days.

ExampleLabels, text functions and dates

SELECT customer_name,
       'Customer #' || customer_id     AS label,
       upper(city)                     AS city_upper,
       customer_name || ' (' || city || ')' AS name_and_city,
       joined_on + 30                  AS welcome_offer_ends
FROM customers;

Each row gets a label, its city in capitals and a date 30 days after signup. Look at Reem Al-Zahrani, whose city is NULL: upper(NULL) is NULL, and joining anything with NULL using || gives NULL, so her whole name_and_city is NULL. A missing input makes the whole expression unknown; you'll learn to handle that in the NULL lesson.

Now practise

4 exercises on real data, checked in your browser (practice).