PostgreSQL: using a calculated column in the same query

You need to wrap the SELECT statement into a derived table in order to be able to access the column alias:

select cost1,       quantity_1,       cost_2,       quantity_2       total_1 + total_2 as total_3from (    select cost_1,            quantity_1,            cost_2,            quantity_2,            (cost_1 * quantity_1) as total_1,           (cost_2 * quantity_2) as total_2    from data) t

There won't be any performance penalty on that.

(I'm really surprised that your original SQL statement runs at all in a DBMS)

If you don't like wraping entire query with outerquery, you could use LATERAL to calculate intermediate total_1 and total_2:

SELECT cost_1, quantity_1, cost_2, quantity_2, total_1, total_2,       total_1 + total_2 AS total_3FROM data,LATERAL(SELECT cost_1 * quantity_1, cost_2 * quantity_2) AS s1(total_1,total_2);

DBFiddle Demo

Output:

╔═════════╦═════════════╦═════════╦═════════════╦══════════╦══════════╦═════════╗║ cost_1  ║ quantity_1  ║ cost_2  ║ quantity_2  ║ total_1  ║ total_2  ║ total_3 ║╠═════════╬═════════════╬═════════╬═════════════╬══════════╬══════════╬═════════╣║      1  ║          2  ║      3  ║          4  ║       2  ║      12  ║      14 ║║      3  ║          5  ║      7  ║          9  ║      15  ║      63  ║      78 ║║     10  ║          5  ║     20  ║          2  ║      50  ║      40  ║      90 ║╚═════════╩═════════════╩═════════╩═════════════╩══════════╩══════════╩═════════╝

As a rule, there a two things you need to know about the SELECT clause:

• Although it is written first, it is evaluated last, with the exception of the ORDER BY clause. This is why you cannot use any calculated fields or aliases in any other clause (particularly the WHERE clause) except in the ORDER BY clause.
• Calculations in the SELECT clause are performed in parallel, or at least are handled as if they are. This is why you cannot use one calculation as part of another.

So, the short answer is that you can’t, and that is by design.

The notable exception to this is Microsoft Access, where you can indeed use calculations in subsequent columns and WHERE clauses. However, although that is convenient, it’s not actually an advantage: not following the above principals is less efficient. But it’s OK for light duty databases, which is what Access is supposed to be used for.

If you really want re-use calculated results, you will need a separate query, either in the form of a sub-query or as a Common Table Expression. CTEs are much easier to work with, as they are clearer to read.

Edit

Here is an example why using calculated columns could cause confusion. In Australia we measure height in centimetres, but there still some places which use the ancient inches (1 in = 2.54 cm).

SELECT    id,    height/2.54 as height, -- cm -> in    case when height>175 then 'tall' else '' end as commentFROM people;

Here the CASE still uses the original height value.