CREATE VIEW syntax in PostgreSQL

Introduction

A view lets you save a query as a virtual table. It helps you reuse complex queries easily without rewriting them.

You want to simplify complex queries for others to use.

You need to show only specific columns or rows from a table.

You want to hide sensitive data from users.

You want to organize data in a way that fits your reports.

You want to reuse the same query logic in multiple places.

Syntax

PostgreSQL

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

The view_name is the name you give to your virtual table.

The SELECT statement defines what data the view will show.

Examples

This view shows only active customers with their id, name, and email.

PostgreSQL

CREATE VIEW active_customers AS
SELECT id, name, email
FROM customers
WHERE active = true;

This view shows product IDs and their prices from the products table.

PostgreSQL

CREATE VIEW product_prices AS
SELECT product_id, price
FROM products;

This view shows orders placed in the last 30 days.

PostgreSQL

CREATE VIEW recent_orders AS
SELECT order_id, customer_id, order_date
FROM orders
WHERE order_date > CURRENT_DATE - INTERVAL '30 days';

Sample Program

This example creates an employees table, adds some data, then creates a view called sales_team that shows only employees in the Sales department. Finally, it selects all rows from the view.

PostgreSQL

CREATE TABLE employees (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100),
  department VARCHAR(50),
  salary NUMERIC
);

INSERT INTO employees (name, department, salary) VALUES
('Alice', 'Sales', 50000),
('Bob', 'HR', 45000),
('Charlie', 'Sales', 55000);

CREATE VIEW sales_team AS
SELECT id, name, salary
FROM employees
WHERE department = 'Sales';

SELECT * FROM sales_team;

Important Notes

Views do not store data themselves; they run the saved query each time you use them.

You can update data through views only if they are simple enough (like selecting from one table without joins).

Use views to improve security by showing only needed data.

Summary

CREATE VIEW saves a SELECT query as a virtual table.

Views help simplify and reuse queries.

Views show data dynamically based on the underlying tables.