PostgreSQL Views Explained with Examples

When working with databases, you often run the same complex queries repeatedly.
Views help simplify this by allowing you to save a query as a virtual table.

In this article, you will learn:

• What views are
• Why views are useful
• How to create and use views
• Types of views in PostgreSQL
• When to use views

This guide is written for beginners.

---

What Is a View in PostgreSQL?

A view is a virtual table based on the result of a SQL query.

Key points:

• A view does not store data itself
• It stores a SQL query
• Data is fetched from underlying tables when the view is used

Think of a view as a saved SELECT query.

---

Why Use Views?

Views are useful because they:

• Simplify complex queries
• Improve readability
• Provide data security
• Avoid repeated SQL code
• Present data in a clean format

Views make databases easier to work with.

---

Basic Example of a View

Suppose you have this table:

CREATE TABLE users (

id SERIAL PRIMARY KEY,

name TEXT,

email TEXT,

is_active BOOLEAN

);

---

Creating a View

Basic Syntax

CREATE VIEW view_name AS

SELECT columns

FROM table;

---

Example

CREATE VIEW active_users AS

SELECT id, name, email

FROM users

WHERE is_active = true;

This view stores all active users.

---

Using a View

You query a view just like a table.

SELECT * FROM active_users;

PostgreSQL fetches the data from the original table automatically.

---

Updating Data Through a View

Simple views can be updatable.

Example:

UPDATE active_users

SET email = 'newemail@example.com'

WHERE id = 1;

PostgreSQL updates the data in the original users table.

---

Limitations of Updating Views

Views may not be updatable if they contain:

• Joins
• Aggregations
• GROUP BY
• DISTINCT

Such views are read‑only.

---

Views with Joins

Views can be created using multiple tables.

Example Tables

CREATE TABLE orders (

id SERIAL PRIMARY KEY,

user_id INTEGER,

total NUMERIC

);

View Using Join

CREATE VIEW user_orders AS

SELECT u.name, o.total

FROM users u

JOIN orders o ON u.id = o.user_id;

This view combines user and order data.

---

Replacing a View

To modify an existing view:

CREATE OR REPLACE VIEW active_users AS

SELECT id, name

FROM users

WHERE is_active = true;

This replaces the previous view definition.

---

Dropping a View

To remove a view:

DROP VIEW active_users;

---

Materialized Views

A materialized view stores the result of the query physically.

Unlike normal views:

• Data is stored on disk
• Faster for large datasets
• Must be refreshed manually

---

Creating a Materialized View

CREATE MATERIALIZED VIEW active_users_mv AS

SELECT id, name, email

FROM users

WHERE is_active = true;

---

Refreshing a Materialized View

REFRESH MATERIALIZED VIEW active_users_mv;

---

Normal View vs Materialized View

Normal View:

• No data storage
• Always fresh data
• Slower for complex queries

Materialized View:

• Stores data
• Faster performance
• Needs manual refresh

---

Best Practices for Views

• Use views to simplify complex queries
• Use views for data security
• Avoid too many nested views
• Use materialized views for heavy queries
• Name views clearly

Views improve code readability and maintenance.

---

Summary

• Views are virtual tables
• Views store SQL queries
• Views simplify complex logic
• Data remains in original tables
• Materialized views store data physically

You now understand PostgreSQL views.

---

What’s Next?

Next, we will learn about functions in PostgreSQL.

Next article:
PostgreSQL Functions Explained with PL/pgSQL Examples