How to Create a View in PostgreSQL

In this tutorial, you’ll learn how to create, use, and manage views in PostgreSQL to simplify your queries and improve database organization.

What is a View?

A view in PostgreSQL is a virtual table that is based on the result of a SQL query. It acts like a table but does not store data physically. Instead, it dynamically presents data when queried, making it useful for simplifying complex SQL and enhancing security.

Benefits of Using Views

• Simplifies complex SQL queries
• Improves query readability
• Enhances data security by exposing only specific columns
• Provides a layer of abstraction over base tables

How to Create a View

Here’s the basic syntax:

CREATE VIEW view_name AS
SELECT column1, column2
FROM table_name
WHERE condition;

Example

Let’s create a view named high_salary_employees that shows employees earning more than $70,000:

CREATE VIEW high_salary_employees AS
SELECT id, name, salary
FROM employees
WHERE salary > 70000;

Querying a View

You can query a view just like a table:

SELECT * FROM high_salary_employees;

Updating a View

If you need to change the definition of a view, use CREATE OR REPLACE VIEW:

CREATE OR REPLACE VIEW high_salary_employees AS
SELECT id, name, salary, department
FROM employees
WHERE salary > 70000;

Dropping a View

To remove a view from the database:

DROP VIEW high_salary_employees;

Tips

• Avoid using SELECT * in views to improve performance and stability.
• Name views meaningfully to reflect their purpose.
• Use views to enforce access control by limiting sensitive columns.