Learn SQL with PostgreSQL - Views, Materialized Views & Generated Columns

#Introduction

Welcome to episode 12 of the Learn SQL with PostgreSQL series! So far we've always written the same queries over and over: joining several tables, filtering with the same conditions, the same aggregations. Imagine an application team with 10 developers each writing similar SELECT ... JOIN ... WHERE queries for sales reports — each of them could get a different condition wrong. There's a far cleaner way: store the query as a database object.

In this episode, we'll cover three database objects that make developers' lives easier: standard views that wrap complex queries into "virtual tables", materialized views that store query results physically on disk for maximum speed, and generated columns that compute a column's value automatically from other columns. All three are the foundation for building a clean and scalable data layer.

#Standard Views: Virtual Tables

A View is a named and stored SELECT query. It behaves like a table — it can be SELECTed, joined, even filtered — but it stores no physical data. Every time it's accessed, the query behind the view runs again.

CREATE VIEW v_order_detail AS
SELECT
    o.id AS order_id,
    c.full_name AS customer,
    p.name AS product,
    oi.quantity,
    oi.quantity * p.price AS subtotal
FROM orders o
JOIN customers c ON c.id = o.customer_id
JOIN order_items oi ON oi.order_id = o.id
JOIN products p ON p.id = oi.product_id;

After that, the complex query above can be invoked as simply as:

SELECT * FROM v_order_detail
WHERE customer ILIKE '%budi%';

#Benefits of Using Views

There are three main reasons people use views:

1. Simplifying data access. The application team can simply SELECT from a view that's guaranteed correct, without needing to understand the joins behind it. A complex request becomes a single line.

2. Hiding sensitive columns. A view can expose only specific columns. For instance v_user_public shows name and email but not the password hash or other internal columns. This is a first layer of security (in episode 16 we add the RLS layer).

CREATE VIEW v_user_public AS
SELECT id, email, full_name, created_at
FROM users;

3. Logic consistency. If the reporting logic changes (e.g. the "active product" definition changes), you only need to change one view — all its users automatically follow.

#Altering and Dropping Views

CREATE OR REPLACE VIEW v_order_detail AS
SELECT ... kondisi_baru ...;
 
DROP VIEW v_order_detail;

CREATE OR REPLACE allows replacing a view's definition without dropping it. Note: columns removed from an old view can break application code that still references them — so alter views with care.

#Materialized Views: Physical Results on Disk

A materialized view differs from a regular view: it stores the query result physically to disk. Access is very fast because the data is already available — but it isn't always up to date; you must refresh it periodically.

#When to Use a Materialized View?

A materialized view is the answer to expensive analytical queries (aggregating millions of rows) whose data doesn't change every second. Example: daily sales reports. If every dashboard request ran the full aggregation from scratch, the server would be overwhelmed. With a materialized view, the result is computed once then read many times.

CREATE MATERIALIZED VIEW mv_daily_sales AS
SELECT
    DATE_TRUNC('day', o.created_at) AS day,
    COUNT(*) AS total_orders,
    SUM(o.total) AS revenue
FROM orders o
GROUP BY 1;

#REFRESH: Updating Data

REFRESH MATERIALIZED VIEW mv_daily_sales;

The problem is that a plain REFRESH MATERIALIZED VIEW locks the view while it runs — reading applications have to wait. The solution is REFRESH ... CONCURRENTLY:

CREATE UNIQUE INDEX mv_daily_sales_idx ON mv_daily_sales (day);
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_daily_sales;

#View vs Materialized View

#Generated Columns: Automatically Computed Values

A generated column is a column whose value is computed automatically from other columns on the same row. It is stored physically on disk and updated automatically every time a row is inserted or updated.

The syntax in PostgreSQL: GENERATED ALWAYS AS (expression) STORED — note that PostgreSQL only supports STORED, not VIRTUAL.

CREATE TABLE order_items (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    order_id UUID NOT NULL,
    product_id UUID NOT NULL,
    quantity INTEGER NOT NULL CHECK (quantity > 0),
    price NUMERIC(12,2) NOT NULL CHECK (price >= 0),
    subtotal NUMERIC(12,2) GENERATED ALWAYS AS (quantity * price) STORED
);

Now on every insert or update, subtotal is computed automatically by the database:

INSERT INTO order_items (order_id, product_id, quantity, price)
VALUES ('...', '...', 3, 25000)
RETURNING subtotal;

subtotal immediately becomes 75000 without us writing it — and more importantly, its value can never be out of sync because the database always recomputes it.

#Generated Columns vs Default

Don't confuse them with DEFAULT:

• DEFAULT is filled once at insert time, and can be overridden by the application.
• A generated column is always computed by the database, and can't be inserted or updated manually — PostgreSQL will reject it.

INSERT INTO order_items (order_id, product_id, quantity, price, subtotal)
VALUES ('...', '...', 2, 10000, 99999);

Error: cannot insert a non-DEFAULT value into column "subtotal". The database protects its own consistency.

#Common Mistakes

#Closing

In this episode 12, we've met three important database objects: standard views to simplify access and hide sensitive columns, materialized views for high-speed analytical queries with concurrent refresh, and generated columns that consistently compute values automatically from other columns.

Key takeaways:

• View is a named query — it stores no data, is always current, and is great for simplifying and securing access.
• Materialized view stores physical results on disk — very fast, but must be refreshed.
• REFRESH ... CONCURRENTLY needs a unique index — use it so readers aren't blocked.
• Generated column is computed automatically (GENERATED ALWAYS AS (...) STORED) and cannot be overridden.
• Choose view/matview based on the trade-off between speed and data freshness.

In the next episode, episode 13, we write code inside the database: Stored Procedures, Functions (PL/pgSQL) & Triggers — from User-Defined Functions in the PL/pgSQL language, the difference between functions and stored procedures, to triggers for automation such as an updated_at column and audit logging.