What Are Views?
A view is a saved SQL query that you can use like a table. Instead of writing the same complex query again and again, you create a view once and then SELECT from it. Every time you query the view, PostgreSQL runs the underlying query and returns fresh results.
Think of a view as a "virtual table" โ it doesn't store data. It stores the query definition, and when you select from it, the query executes and returns results in real time. Views are perfect for:
- Reusability: Query the view instead of rewriting complex logic.
- Clarity: Give complex calculations a meaningful name.
- Security: Hide sensitive columns by showing only what users need.
- Abstraction: Isolate application code from table schema changes.
Views for Business Logic
Let's create a view that calculates daily revenue metrics for the NovaPay payment gateway. This query is expensive to run repeatedly, so saving it as a view makes it easy for your analytics team:
Q1: Create the daily_revenue View
Breakdown: This view groups successful transactions by date and calculates total revenue, count, and average amount. Now your analysts can simply SELECT from this view instead of rewriting the JOIN and aggregation logic every time.
Q2: Query the daily_revenue View
โโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโ
2025-06-30 | 1 | 28093.71 | 1 | 28093.71
2025-06-28 | 2 | 26728.33 | 2 | 13364.17
2025-06-27 | 1 | 35713.48 | 1 | 35713.48
2025-06-26 | 1 | 16956.29 | 1 | 16956.29
2025-06-18 | 2 | 44340.04 | 2 | 22170.02
Q3: Create the merchant_summary View
Now let's create a view that shows each merchant's transaction stats for business intelligence:
Q4: Query the merchant_summary View
โโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโ
50 | FitnessPlan Pro | food | Nairobi| active | 14 | 681080.83 | 52390.83
21 | MegaMart Mumbai | retail | Nairobi| active | 13 | 546079.08 | 60675.45
7 | SpiceBowl Kitchen | travel | Nairobi| active | 14 | 490635.21 | 44603.20
Materialized Views
A materialized view is different from a regular view. It stores the results on disk, like a snapshot. When you query it, you get instant results instead of re-running the query. The trade-off: data becomes stale until you refresh it.
Materialized views are ideal for:
- Expensive queries: Queries that take seconds or minutes to compute.
- Analytics dashboards: Data that can be slightly delayed but must be fast.
- Heavy aggregations: Queries with many JOINs and GROUP BY clauses.
Q5: Create a Materialized View for Payment Method Stats
Q6: Query the Materialized View
โโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโ
bank_transfer | 201 | 4688021.37 | 23323.49 | 20 | 9.95
card | 178 | 4558228.46 | 25608.03 | 25 | 14.04
upi | 89 | 2126859.11 | 23897.29 | 10 | 11.24
mpesa | 32 | 1539073.96 | 48096.06 | 6 | 18.75
Q7: Refresh a Materialized View
To update the stored results with fresh data, use REFRESH MATERIALIZED VIEW:
Note: This command updates the stored snapshot with current data from the base tables. If new transactions arrived since the last refresh, the stats will now reflect them.
VIEW vs MATERIALIZED VIEW: A regular VIEW re-runs the query every time you SELECT. A MATERIALIZED VIEW stores results and re-runs only when you REFRESH. If your query is slow and data can be a few minutes old, use materialized; if you need real-time data, use regular views.
Views for Security
Views are powerful for implementing row-level and column-level security. Hide sensitive columns and expose only what users should see:
Q8: Create a View for High-Risk Transactions
Let's create a view that the fraud team can use without exposing merchant passwords or full customer data:
Q9: Query the high_risk_transactions View
โโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโ
3 | 28 | CloudHosting KE | 25666.55 | INR | card | 2025-04-14 12:06 | 84
6 | 22 | TiffinService | 723.14 | USD | card | 2024-06-22 12:55 | 76
9 | 48 | LogAnalyzer | 99279.16 | KES | card | 2025-03-11 08:24 | 97
15 | 28 | CloudHosting KE | 29264.97 | INR | upi | 2025-02-16 14:35 | 83
Security advantages: The fraud team can SELECT from this view without seeing merchant passwords or other sensitive columns. You control exactly what's exposed. Grant the fraud team only this view's permissions, not the base tables.
When to Use Each
Use a regular VIEW when:
- You need real-time, up-to-date results every time.
- The query is fast enough to run repeatedly.
- You're simplifying a moderately complex query for readability.
- You're implementing security (hiding columns or rows).
- Query results change frequently (transactions, logs).
Use a MATERIALIZED VIEW when:
- The underlying query is expensive (many JOINs, heavy aggregations).
- Data can be a few minutes or hours old.
- You're building an analytics dashboard or report.
- You need fast query performance for reading heavy workloads.
- Refresh can happen on a schedule (e.g., every hour).
Challenge
Create two views for the NovaPay team:
- top_merchants_by_revenue โ A view that shows the top 10 merchants by total successful transaction volume. Include merchant_name, category, city, total_volume, and transaction_count.
- daily_failure_rate โ A materialized view showing the failure rate per day. Include the date, total_transactions, failed_transactions, and failure_rate (as a percentage).
Then query both views and verify they return results.
For the first view, use SUM with CASE to count only successful transactions. For the second, filter by status='failed' in your COUNT.
Test Your Knowledge
Views mastered. Your SQL is production-ready.
Join the Best AI Program: The AI-Era Data Engineer โ 5 months, fully online, with placement assistance. Week 1 is free.
Explore The AI-Era Data Engineer โ