Calculated Columns (PostgreSQL)

So far, we’ve learned how to retrieve data from a table using SELECT.

At this point, it might look like SQL can only return the exact data that is already stored in the database.

But SQL is much more powerful than that.

With SQL, we can calculate new values on the fly while fetching data.

These are called calculated columns.

---

Why Do We Need Calculated Columns?

Let’s look at our cities table again.

We already store:

• population

• area

From this data, we can calculate a very useful metric:

Population Density
(population ÷ area)

This tells us how many people live per square kilometer in a city.

Instead of manually calculating this for each city, SQL can do it for us.

---

Calculating Values in a SELECT Query

Let’s write a query that:

• selects the city name

• calculates population density

select
  name,
  population / area
from cities;

What’s happening here?

• population / area is a calculation

• PostgreSQL performs this calculation for every row

• The result is shown as a new column in the output

This new column does not exist in the table.
It is created temporarily for this query only.

---

Temporary or “Imaginary” Columns

You can think of calculated columns as:

• temporary

• virtual

• created only at query time

They:

• are not stored in the database

• exist only in the query result

---

Using Other Math Operators

We are not limited to division only.

PostgreSQL supports many math operations, such as:

• + addition

• - subtraction

• * multiplication

• / division

Example: Adding Two Columns

select
  name,
  population + area
from cities;

This query adds population and area for each city.

---

Example: Multiplication (Integer Limit)

select
  name,
  population * area
from cities;

This will result in an error.

Why?

Because:

• PostgreSQL integer values have a limit (~2.14 billion)

• multiplying large numbers exceeds that limit

This is a good reminder that:

data types matter, even in calculations.

---

Column Name Problem (?column?)

When we run this query:

select
  name,
  population / area
from cities;

PostgreSQL shows the calculated column name as:

?column?

This happens because:

• PostgreSQL knows we calculated something

• but it doesn’t know what to call it

This name is not very helpful.

---

Renaming a Calculated Column (Using AS)

To give our calculated column a meaningful name, we use AS.

select
  name,
  population / area as population_density
from cities;

Now the output column is clearly named:

population_density

Much better 👍

---

Column Aliases Are Flexible

We can name the column anything we want.

select
  name,
  population / area as density
from cities;

Both queries work the same — only the column name changes.

---

Key Points to Remember

• SQL can calculate values while fetching data

• Calculated columns are temporary

• Math operators work directly on column values

• Use AS to rename calculated columns

• Data type limits still apply during calculations