PostgreSQL -How to Create a View
You can create a view using the CREATE VIEW statement in PostgreSQL database. A view is a virtual table that is based on the result of a SELECT query. Views allow you to present a subset of data from one or more tables in a structured way, making it easier to query and retrieve specific information. Here’s the basic syntax to create a view:
CREATE VIEW view_name AS SELECT column1, column2, ... FROM table_name WHERE condition;where
view_nameis the name you want to give to the view.column1, column2, ...are the columns you want to include in the view.table_nameis the name of the table(s) from which you want to create the view.condition(optional) is an optional condition to filter the rows in the view. This is essentially aWHEREclause in the SELECT statement.
Consider a table named “employees” with columns “employee_id,” “first_name,” “last_name,” and “salary,” and you want to create a view that shows only the employee ID and full name for employees with a salary greater than 50000:
CREATE VIEW high_salary_employees AS SELECT employee_id, first_name || ' ' || last_name AS full_name FROM employees WHERE salary > 50000;In this example:
high_salary_employeesis the name of the view.- We are selecting the “employee_id” and concatenating “first_name” and “last_name” to create a “full_name” column.
- We’re filtering the rows to include only those with a salary greater than 50000.
After creating the view, you can query it like you would any regular table:
SELECT * FROM high_salary_employees;This will return the results based on the view’s definition.
Views can simplify complex queries, provide a consistent interface to the data, and help enhance security by limiting the data that users can access directly. Keep in mind that views do not store data themselves; they are just a way to present data from underlying tables.