Mastering PostgreSQL Databases: From Basics to Advanced/

...

/

Working with Views in PostgreSQL

Working with Views in PostgreSQL

Learn how to create and query a view in PostgreSQL.

We'll cover the following...

Database view

A database view is a virtual table created from the results of one or more SELECT statements. A view can be used to simplify the process of querying data or to provide security by hiding the underlying structure of the data.

Databases are often divided into multiple tables, with each table containing a subset of the data. This allows for better organization and easier maintenance but can complicate querying the data. A database view allows us to create a virtual table based on one or more existing tables. The base tables used to create the database view are hidden from the end user. This simplifies querying the data because only the view needs to be queried. Views can also be used for performance optimization, because the query to create the view only needs to be executed once, and the resulting data can be accessed multiple times.

Note: Changes to the underlying tables will not automatically update the view. The view must be refreshed, or the query rerun to reflect any changes. Overall, database views can improve the organization and functionality of a database by allowing users to access only relevant data and simplifying complex queries.

Creating a view

To create a view, we use the CREATE VIEW statement followed by the view name and the query that defines it. The query can include multiple tables and use conditional statements, aggregate functions, and any other SQL capabilities.

Press + to interact
CREATE VIEW <view_name> AS
SELECT
  <column_1>,
  <column_2>, 
  ...
  <column_n>
FROM
  <table_name>;

Here, <view_name> represents the name of the view that we want to create, and <table_name> represents the table from which we to fetch the data. In case required, we can specify a condition for the selection criteria in the WHERE clause.

Press + to interact
CREATE VIEW OrderHistory AS 
SELECT 
    Customer.name, 
    Customer_order.order_date 
FROM 
    Customer INNER JOIN Customer_order 
    ON Customer.order_id = Customer_order.id;
\d+ OrderHistory

The statement above creates a view called OrderHistory which includes the customer’s details and their corresponding orders, by joining the Customer and Customer_order table on the common id field. The command \d+ Customer_order prints out the details of the view.

Note: Views don’t physically store data—they’re a ...