How to Manage PostgreSQL Views

Database view is basically a stored query, that allows you to access data from one or more tables. These don’t contain any data but their underlying query is executed on the fly, whenever they are accessed. They are also known as virtual tables. Database views are useful because they provide a shortcut identifier to access a query. So it can be used to store large queries easily. In this article, we will learn how to manage PostgreSQL views.

How to Manage PostgreSQL Views

Here is the syntax to manage PostgreSQL views.

CREATE VIEW view_name AS query;

In the above command, you need to specify the name of view followed by the query to populate it. It is generally a SELECT query. You can have simple or complex SQL queries in a view, as per your requirement.

Let us say you have the following SQL query that you want to store as view.

postgres# select id, name, sale from customers;

Here is the query to create a view get_customers for this query.

postgres# create get_customers as 
          select id, name, sale from customers;

Once you have created the view, you can treat it as a table and use it in your queries.

postgres# select * from get_customers;

The above query will produce the same result as your original SQL query.

Modify PostgreSQL View

If you want to modify the underlying query to an existing view, you need to use CREATE OR REPLACE statement.

CREATE OR REPLACE view_name as query;

Here is an example to modify the query for get_customers view.

postgres# CREATE OR REPLACE get_customers as
select id, name, sale from customers where sale>100;

But please note, the new query of your view must contain the same columns as the previous query of your view. Otherwise, you will get the following error.

“[Err] ERROR:  cannot drop columns from view”

In other words, they should have the same name, data type and order as the original SQL query. The following query will give you an error because the original SQL query contains id, name and sale columns.

postgres# CREATE OR REPLACE get_customers as
          select id, name from customers where sale>100;
postgres# “[Err] ERROR:  cannot drop columns from view”

If you want to rename a view, you need to use ALTER VIEW statement. Here is an example to rename view get_customers to select_customers.

postgres# ALTER VIEW get_customers RENAME TO select_customers;

Remove PostgreSQL view

If you want to remove PostgreSQL view, you need to use DROP VIEW.

DROP VIEW [ IF EXISTS ] view_name;

Generally, we specify the view_name after DROP VIEW clause but if the view does not exist, then the database will give an error. So it is better to use IF EXISTS keyword for this purpose. Here is the query to drop the get_customers view.

DROP VIEW IF EXISTS get_customers;

In this case, PostgreSQL will drop the view only if it exists, else it will do nothing.

In this article, we have learnt how to create, modify & remove database views in PostgreSQL. PostgreSQL database views are very convenient way to store long queries. You can easily refer to them using the view name, without actually remembering the full query. You can also use it other queries as a table, without having to type/paste the entire query. Generally, database views are very useful data reporting and analytics in organizations.

How to Manage PostgreSQL Views