How To Create a View On PostgreSQL

 

In PostgreSQL, views are logical tables.

By turning queries into views we can use complex queries with a simple select statement.

For example, if you put a query that joins several tables into a view with view_x name, you can query this view with the following script.

 

Thus, by running a complex query with a simple select expression, you reduce the complexity of the application.

We talked about the fact that the data are not physically stored in the views.

If you want to keep the data physically in view, you should create a materialized view.

For more detailed information on materialized views, you may want to read the article entitled “How To Create a Materialized View On PostgreSQL“.

Below is a simple example of using views.

First, we create a table with the help of the following script.

 

We then add a record to this table with the help of the following script and then query the table to check it later.

 

Then we create a view that queries this table with the help of the following script.

 

You can then query the view as follows.

 

How do we alter views?

First, let’s add a column to the table.

 

Later we will update the table to fill in the column we added.

 

Let’s check the contents of the view again with the following select script.

 

The city column did not come as you see it.

Because there was no such column when we created the view.

Now we update the view with the following script and then check again.

 

 

Let’s try to delete a column from the View.

 

We got an error like you saw it. “can not drop columns from view”

You can delete and recreate the view as follows.

 

You may want to look at the following articles about Views.

How To Create a Materialized View On PostgreSQL“,

How To Create a Recursive View On PostgreSQL“,

How To Create an Updateable View On PostgreSQL“,

How To Create an Updateable View WITH CHECK CONSTRAINT On PostgreSQL

dbtut

We are a team with over 10 years of database management and BI experience. Our Expertises: Oracle, SQL Server, PostgreSQL, MySQL, MongoDB, Elasticsearch, Kibana, Grafana.

Leave a Reply