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.

CREATE OR REPLACE VIEW  updateable_view_example AS
SELECT name,city from table_example
WHERE city='NEWYORK'
WITH CHECK OPTION;

CREATE OR REPLACE VIEW updateable_view_example AS

SELECT name,city from table_example

WHERE city='NEWYORK'

WITH CHECK OPTION;

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).

INSERT INTO updateable_view_example (name,city)
VALUES('Donnie Yen','Guangzhou');

1 2	INSERT INTO updateable_view_example (name,city) VALUES('Donnie Yen','Guangzhou');

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

INSERT INTO updateable_view_example (name,city)
VALUES('Donnie Yen','NEWYORK');

1 2	INSERT INTO updateable_view_example (name,city) VALUES('Donnie Yen','NEWYORK');

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

Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux