How to Create a View in PostgreSQL: Syntax and Examples

In PostgreSQL, you create a view using the CREATE VIEW view_name AS SELECT ... statement. A view is like a saved query that you can treat as a table for easier data access and reuse.

Syntax

The basic syntax to create a view in PostgreSQL is:

• CREATE VIEW view_name AS: Defines the name of the view you want to create.
• SELECT ...: The query that defines the data the view will show.

This creates a virtual table that you can query like a regular table.

sql

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

Example

This example creates a view named active_customers that shows customers with status 'active'. You can then query active_customers like a normal table.

sql

CREATE TABLE customers (
  id SERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  status TEXT NOT NULL
);

INSERT INTO customers (name, status) VALUES
('Alice', 'active'),
('Bob', 'inactive'),
('Carol', 'active');

CREATE VIEW active_customers AS
SELECT id, name
FROM customers
WHERE status = 'active';

SELECT * FROM active_customers;

Output

 id | name 
----+-------
  1 | Alice
  3 | Carol
(2 rows)

Common Pitfalls

Common mistakes when creating views include:

• Not specifying column names clearly in the SELECT statement, which can cause confusion.
• Trying to create a view on a table or column that does not exist.
• Assuming views store data; they only store the query, so changes in underlying tables affect the view.
• Using CREATE VIEW without OR REPLACE when updating an existing view, causing errors.

sql

/* Wrong: Creating a view without existing table */
CREATE VIEW wrong_view AS
SELECT * FROM non_existing_table;

/* Right: Create view on existing table */
CREATE VIEW correct_view AS
SELECT * FROM customers;

Quick Reference

Command	Description
CREATE VIEW view_name AS SELECT ...	Create a new view with the given query
CREATE OR REPLACE VIEW view_name AS SELECT ...	Update an existing view or create if it does not exist
DROP VIEW view_name;	Delete a view
SELECT * FROM view_name;	Query data from the view

Key Takeaways

Use CREATE VIEW with a SELECT query to define a reusable virtual table.

Views do not store data; they reflect the current data in underlying tables.

Use CREATE OR REPLACE VIEW to update an existing view without errors.

Always ensure the tables and columns in the view query exist.

Query views like regular tables to simplify complex queries.