Postgres Computed Column

In SQL databases, a computed column refers to a “dynamic” column that calculates the data from other columns within the same table. For example, if you want to store a column that determines the total cost of items, you can create a computed column that multiplies the total items multiplied by the price of each item.

Computed columns are an essential tool to store the data that is regularly updated and inconsistent over a period of time.

Unlike MySQL and SQL servers, PostgreSQL does not natively support the computed columns as a built-in feature. However, we can implement similar functionality using triggers, functions, and table views.

In this post, we will explore how to implement the computed columns in PostgreSQL by using triggers, functions, and views.

PostgreSQL Computed Column: Triggers and Functions

The most basic method of creating a computed column in PostgreSQL is triggers and functions. This allows us to develop triggers for events such as an update or insert to the target table which invoke the function to update the table.

Let us demonstrate.

Consider a table called “sales” that stores the sale information. We also have a computed column that calculates the total price based on the quantity and the cost per item.

CREATE TABLE sales (
id SERIAL PRIMARY KEY,
quantity INT,
price_per_item DECIMAL(5, 2),
total_price DECIMAL(5, 2)
);

Add sample data to the table is as follows:

INSERT INTO sales(quantity, price_per_item)
VALUES (5, 10.50),
(3, 15.00),
(10, 8.99),
(2, 25.00),
(7, 5.00),
(8, 10.00),
(6, 15.50),
(4, 20.00),
(1, 30.00),
(5, 10.00);

Output Table:

Create a Function

The next step is to define a function that calculates the total price. This function is triggered if a new row is added to the table or updated.

The function definition is as follows:

CREATE OR REPLACE FUNCTION calculate_total_price()
RETURNS TRIGGER AS $$
BEGIN
NEW.total_price = NEW.quantity * NEW.price_per_item;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;

This function multiplies the quantity value with the price_per_item and returns the total value.

Create a Trigger

Finally, we can define a trigger that runs when the an INSERT or UPDATE operation is carried out on the sales table.

The trigger definition is as follows:

CREATE TRIGGER sales_trigger
BEFORE INSERT OR UPDATE ON sales
FOR EACH ROW
EXECUTE FUNCTION calculate_total_price();

When we insert or update a row in the “sales” table, the “total_price” column is automatically updated to reflect the current values.

PostgreSQL Computed Column: Using the Table Views

We can also use the table views to create a computed column for a given table. A view is like a standalone snapshot of a given table which allows us to query and perform actions on it without affecting the original table.

We can create a view that includes the computed column for the “sales” table as demonstrated in the following example query:

CREATE VIEW sales_view AS
SELECT id, quantity, price_per_item, quantity * price_per_item AS total_price
FROM sales;

This creates a table view for the sales table that we can query just like a regular table with the values of the “total_price” column.

Example:

select * from sales_view;

Output:

Conclusion

Following this post, you now learned how to create the computed columns in PostgreSQL using triggers and functions or views.