How To Create an Updatable View WITH CHECK OPTION in PostgreSQL

I described creating an updateable view in the article “How To Create an Updatable View in PostgreSQL“.

In this article I will describe how to create an updateable view with WITH CHECK OPTION.

In the previous article, although the filter in the view is NEWYORK, we could insert it as LONDON in the city name.

This could create security vulnerability.

If we create an updateable view with WITH CHECK OPTION, we only allow changes to the recordset that the view has access to.

I recreate the updateable view as follows.

Then, let’s try to add a record that does not have the city name NEWYORK as in our previous example. We will get an error like below.

ERROR:  new row violates check option for view “updateable_view_example”

DETAIL:  Failing row contains (Donnie Yen, Guangzhou).

If you change the city name to NEWYORK, you can successfully complete the INSERT.

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 On 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 *