Postgres How to Implement Calculated Column with Clause

Postgres how to implement calculated column with clause

If you don't want to repeat the expression, you can use a derived table:

select *
from (
   select id, cos(id) + cos(id) as op 
   from myTable 
) as t 
WHERE op > 1;

This won't have any impact on the performance, it is merely syntactic sugar required by the SQL standard.

Alternatively you could rewrite the above to a common table expression:

with t as (
  select id, cos(id) + cos(id) as op 
  from myTable 
)
select *
from t 
where op > 1;

Which one you prefer is largely a matter of taste. CTEs are optimized in the same way as derived tables are, so the first one might be faster especially if there is an index on the expression cos(id) + cos(id)

Postgresql subqueries using a calculated column

I'm a bit confused with your notation, but it looks like there are parenthesis issues: your from statement is not linked to the select.

In my opinion, the best way to manage subqueries is to wrinte someting like this :

WITH query1 AS (
   select col1, col2
      from table1
),
query2 as (
   select col1, col2
      from query1
   (additional clauses)
),
select (what you want)
 from query2
(additional statements)

Then you can manipulate your data progressively until you have the right organisation of your data for the final select, including aggregations

Postgres add column with initially calculated values

I discovered a simple way! The following adds the value3 column with the desired initial values:

ALTER TABLE numbers
ADD COLUMN value3 INTEGER; -- Exclude the NOT NULL constraint here

UPDATE numbers SET value3=value1+value2; -- Insert data with a regular UPDATE

ALTER TABLE numbers
ALTER COLUMN value3 SET NOT NULL; -- Now set the NOT NULL constraint

This method is good when postgres has a native function for the calculation you want to apply to the new column. E.g. in this case the calculation I want is "sum", and postgres does that via the + operator. This method will be more complex for operations not natively provided by postgres.

Computed / calculated / virtual / derived columns in PostgreSQL

Postgres 12 or newer

STORED generated columns are introduced with Postgres 12 - as defined in the SQL standard and implemented by some RDBMS including DB2, MySQL, and Oracle. Or the similar "computed columns" of SQL Server.

Trivial example:

CREATE TABLE tbl (
  int1    int
, int2    int
, product bigint GENERATED ALWAYS AS (int1 * int2) STORED
);

fiddle

VIRTUAL generated columns may come with one of the next iterations. (Not in Postgres 15, yet).

Related:

• Attribute notation for function call gives error

Postgres 11 or older

Up to Postgres 11 "generated columns" are not supported.

You can emulate VIRTUAL generated columns with a function using attribute notation (tbl.col) that looks and works much like a virtual generated column. That's a bit of a syntax oddity which exists in Postgres for historic reasons and happens to fit the case. This related answer has code examples:

• Store common query as column?

The expression (looking like a column) is not included in a SELECT * FROM tbl, though. You always have to list it explicitly.

Can also be supported with a matching expression index - provided the function is IMMUTABLE. Like:

CREATE FUNCTION col(tbl) ... AS ...  -- your computed expression here
CREATE INDEX ON tbl(col(tbl));

Alternatives

Alternatively, you can implement similar functionality with a VIEW, optionally coupled with expression indexes. Then SELECT * can include the generated column.

"Persisted" (STORED) computed columns can be implemented with triggers in a functionally equivalent way.

Materialized views are a related concept, implemented since Postgres 9.3.

In earlier versions one can manage MVs manually.

Postgres: If we select a computed column multiple times, will Postgres compute it again and again?

I am fairly certain that in a case expression, the when clause is going to be evaluated separately for each condition. That means that your colleague is correct . . . probably.

The operative part of the documentation is:

Each condition is an expression that returns a boolean result. If the condition's result is true, the value of the CASE expression is the result that follows the condition, and the remainder of the CASE expression is not processed.

It is possible that Postgres does do some sort of optimization of subexpressions by evaluating them once. However, the statement: "the remainder of the CASE expression is not processed" is pretty strong and suggests that each clause will only be processed after the previous ones have evaluated to false (or NULL).

Regardless of whether the optimizer picks figures out that a function can be called only once, the lateral join guarantees that it will be evaluated once, so that seems like the safer solution for an expensive operation.

Reuse calculated column in WHERE clause

There is no way to reuse the calculated field on the same level SELECT. You will need to nest it in order to use the alias.

SELECT field1
    , calc_field
FROM (
    SELECT field1
        , CONCAT (field2, field3) AS calc_field
    FROM MyTable
    ) tbl
WHERE calc_field LIKE 'A%'

This is because of the order in which clauses are executed in a SQL query. As you can see in the way the clauses are listed, the SELECT clause, where the alias is generated, is executed after the WHERE clause.

Thus, the alias is not "visible" in the WHERE clause, because the alias is generated after the WHERE is applied.

PostgreSQL Calculated Column with values of another table referenced by foreign key

You cannot define a generated column based on values from other tables. Per the documentation:

The generation expression can refer to other columns in the table, but not other generated columns. Any functions and operators used must be immutable. References to other tables are not allowed.

You can achieve the expected behavior by creating two triggers on both tables but usually creating a view based on the tables is a simpler and more efficient solution.

---

Related Topics