Creating a View
To create a view in PostgreSQL, you use theCREATE VIEW
statement followed by the view name and the query that defines the view.
For example, assuming you have two tables employees
and departments
(we created them in the tables section of this guide),
you can create a view that combines data from both tables:
Updating a View
There are two ways to update a view in PostgreSQL:Recreating the View
You can update a view by replacing it with the updated query. This allows you to modify the underlying query, as long as the column names and data types of existing columns remain the same and in the same order. You can add new columns to the end of theSELECT
statement, and also modify the underlying logic behind the columns as long as the name and type remain the same.
Because the view does not contain any data itself, replacing it is a fast, atomic and idempotent (repeatable) operation.
Because the limitations prevent you from making any breaking changes, the operation is safe to perform online (while the application is running).
For example, if you want to include the employee email in the employee_details
view, you can replace the view as follows:
Altering a View
Some properties of a view can be altered without replacing the view definitions, for example you can change a view name, owner, rename a column or modify default values. For example:Dropping a View
To drop a view in PostgreSQL, you use theDROP VIEW
statement followed by the view name:
CREATE OR REPLACE VIEW
syntax.
You can do this in a single transaction to ensure that the view is always available for querying, but be aware that if you remove columns
that are still in use, you may break the application.