How to Create, Update, and Drop Views in PostgreSQL

The definition of a view in PostgreSQL—and in other SQL database management systems, for that matter—is rather simple: a view is a virtual table that does not form part of the physical schema. It is computed or collated dynamically from data in ordinary tables (a.k.a. base tables). In this article, we are going to show you how to create PostgreSQL views and manage them with ease.

Views can come in handy in a number of cases:

• Generally, you can use views to structure any required data in a convenient, intuitive way.
• If you are dealing with sensitive information, you can create a view with separate permissions to restrict user access to specific columns and rows of a table (or several tables).
• A view can be helpful for recurring operations with a complex query. You can use this query just once to create a dedicated view. Later on, whenever you need it, you can query this view with a simple SELECT statement.
• Views can also be applied to aggregate and summarize data from multiple tables, which is useful for reporting.

CONTENTS
1. How to use the CREATE VIEW statement in PostgreSQL
2. How to create a temporary view
3. How to create a materialized view
4. Creating a view from multiple PostgreSQL tables
5. How to update PostgreSQL views with CREATE OR REPLACE
6. Using the ALTER VIEW command to modify auxiliary properties
7. How to delete a PostgreSQL view
8. How to manage views using dbForge Studio for PostgreSQL
9. Conclusion

How to use the CREATE VIEW statement in PostgreSQL

Let’s start with creating a PostgreSQL view. For that purpose, we use the CREATE VIEW statement. Its basic syntax is simple and looks as follows:

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

Here, you indicate the name of your view after the CREATE VIEW clause and enter your query after the AS keyword. This is the defining query of your view, and it can be of any complexity.

Please note that you may add IF NOT EXISTS to the statement if you are unsure whether a view with this name exists:

CREATE VIEW IF NOT EXISTS view_name AS
SELECT column1, column2.....
FROM table_name
WHERE [condition];

Afterward, you can easily query your newly created view as follows:

SELECT * FROM view_name;

How to create a temporary view

You can add the optional TEMP or TEMPORARY keyword to create a temporary view. This is what the syntax looks like:

CREATE [TEMP | TEMPORARY] VIEW view_name AS
SELECT column1, column2.....
FROM table_name
WHERE [condition];

A temporary view will be automatically deleted at the end of your session.

How to create a materialized view

Although materialized views represent a rather different database object type, we could not leave them unmentioned. Yes, they are defined by a query, just like views proper. However, unlike them, materialized views are disc-stored, and their defining queries are not executed whenever you access them. In order to update the data contained in a materialized view, you need to refresh it with a special query.

Let’s see how it’s done. First, let’s check the basic syntax that creates a materialized view:

CREATE MATERIALIZED VIEW view_name AS
SELECT column1, column2.....
FROM table_name
WHERE [condition];
WITH [NO] DATA;

As you can see, the difference lies in the first and last clauses—and the latter could use some explanation. So, if you want to load data into your new materialized view immediately, use the WITH DATA option. Otherwise, enter WITH NO DATA in your query, and your view will be unreadable until you load data into it.

To load data into a materialized view, or to update it, use the following query:

REFRESH MATERIALIZED VIEW [CONCURRENTLY] view_name;

That’s it! However, take note that whenever you refresh a materialized view, PostgreSQL locks the involved table, so you can’t execute other queries against it until the refresh is complete. Keep this in mind if your application must not be exposed to downtime. In order to prevent this, use the CONCURRENTLY keyword.

Creating a view from multiple PostgreSQL tables

You can create a view from multiple base tables in PostgreSQL by including them in your SELECT statement:

CREATE [TEMP | TEMPORARY] VIEW view_name AS
SELECT column1, column2.....
FROM table_name1, table_name2.....
WHERE [condition];

How to update PostgreSQL views with CREATE OR REPLACE

If you need to update the defining query of an already existing view, you can use the CREATE OR REPLACE statement:

CREATE OR REPLACE view_name AS
SELECT column1, column2.....
FROM table_name
WHERE [condition];

Everything is the same: specify the name of your existing view and enter the updated query after the AS keyword.

You may also refer to our guide dedicated to creating tables in PostgreSQL using CREATE OR REPLACE.

Using the ALTER VIEW command to modify auxiliary properties

ALTER VIEW allows modifying the auxiliary properties of a view (as opposed to modifying the defining query of your view using CREATE OR REPLACE). For instance, you can rename your view with the following syntax:

ALTER VIEW view_name RENAME TO new_view_name;

How to delete a PostgreSQL view

You can delete a view using the DROP VIEW statement. The basic syntax is as follows:

DROP VIEW [IF EXISTS] view_name;

Here you only have to specify the view to be deleted. The IF EXISTS clause is an optional one; if you omit it and try to delete a view that does not exist, you will get an error.

How to manage views using dbForge Studio for PostgreSQL

All of these operations can be conveniently performed in dbForge Studio for PostgreSQL, an IDE that covers the majority of daily tasks related to PostgreSQL development, management, and administration. Its additional capabilities include data editing, import, export, and reporting.

Creating PostgreSQL views

Creating a PostgreSQL view is a matter of moments. On the toolbar, click New SQL and enter the required CREATE VIEW query in the SQL window.

Afterward, simply click Execute, and your view will be created.

By the way, you may consult our blog post to learn more about creating a new PostgreSQL database using dbForge Studio.

Altering PostgreSQL views

Similarly, you can alter PostgreSQL views. The flow is the same: click New SQL, enter the required CREATE OR REPLACE VIEW or ALTER VIEW query, and click Execute.

Removing PostgreSQL views

Finally, the removal of a view is just as fast with the DROP VIEW query:

Note: You can learn all about creating PostgreSQL indexes of different types in How to create indexes in PostgreSQL.

Conclusion

Easy manipulations with views are only a tiny part of what dbForge Studio for PostgreSQL has to offer. If you would like to see all of its capabilities in action, feel free to download a free 30-day trial.