Computed Columns

SELECT can do more than get columns. You can also compute new ones.

How can new values be created? One way is to transform (do computations on) the individual values in an existing column. Transforming a column in this way is called a "computed column".

Suppose we have a table called numbers with just 1 column named x.

A column is computed by putting the transformation in the expression of the SELECT clause. In SQL, you can do multiplication with the * operator.

SELECT
  x,
  x * x AS y
FROM numbers;

The above query can be thought of as a table showing some values of the function y = x², or y = x * x. The y column was computed from x by squaring it. In each row, the individual values of x are put through the expression to compute the new y value per row. Think of the SELECT clause as if it looping over the rows of the input table, and generating the rows of the result table.

Notice that we used an alias to give the name y. That isn't required, but if we don't give it a name, you may not like the result.

SELECT x * x
FROM numbers;

Postgres automatically gives computed columns names if we don't give it an alias, in this case: ?column?.

This example only works if x's type is INTEGER. This is why knowing the column's type is important. You can't square a string of text after all. There is a different set computations that can be done on text.

Text Transformations

Just like numbers have the multiplication operator (*) text has operators too.

Let's go back to our daters table from before. Suppose we want to use the users' eye color to generate some default description of them if they haven't filled it out. Combining text with other pieces of text can be done with ||.

SELECT name || ' has ' || eye_color || ' eyes.' AS description
FROM daters;

Pieces of text can either come from the rows and column in the table, or they can come from literal values that we type into the expression. We can type literal text values using single-quotes '', not double-quotes "" which mean something else. If you've never seen single-quotes before, you type them with the apostrophe key.

Computed Columns With Function Calls

Most computations are actually done with functions, not operators like * or ||. Functions have a readable name, like the upper() function.

SELECT upper(name) AS name
FROM daters;

Here, upper() is a function, and it only takes text types. It converts all characters in the text to upper-case. Postgres includes many functions for text. There is no need to memorize them, you just look them up when you need a specific one. Most people rely on a search engine like Google for that.

There are functions that work on all data types in Postgres, including integers. Before, we squared the x column with x * x. We also could've done this with a function power(x, 2).