How To Create an Updatable View in PostgreSQL

In PostgreSQL, we can perform insert, update and delete operations on the relations specified in the view via views.

Restrictions

Of course there are some restrictions. You can find these restrictions below.

• GROUP BY, HAVING, LIMIT, OFFSET, DISTINCT, WITH, UNION, INTERSECT, and EXCEPT statements can not be used in the query in View.
• Window function, set-returning function or any aggregate function can not be used.

A user who has the authority to update on View does not need to be authorized on the table.

Only the owner of the view needs to be able to update the table.

You can use updateable views in situations where you want certain users to update certain columns of certain tables.

Let’s continue by making an example.

I will use the table named table_example that I created in the article titled “How to Create a View in PostgreSQL“.

When we query the table, the following result is returned.

We create an updateable view using the following script.

We see the results by querying View as follows.

Let’s do an insert operation on the table via View.

We will get an error like below. Because we did not define the city column in the view.

column “city” of relation “updateable_view_example” does not exist.

I recreate the View as follows.

Later, when we try to insert again, we will be able to perform the operation without any errors.

But when we query the view with the Select statement, we will not be able to see the record we added.

Because in the filter in the view, the city must be NEWYORK.

But in the record we insert, the city column is LONDON.

If you query the table with SELECT statement, you can see the record you added.

Let’s end the article by doing an update on the updateable view.

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

How To Create a View On PostgreSQL“,

How To Create a Materialized View On PostgreSQL“,

How To Create a Recursive View On PostgreSQL“,

How To Create an Updatable View WITH CHECK CONSTRAINT in PostgreSQL

dbtut
Author: 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

Your email address will not be published. Required fields are marked *