{"id":1199,"date":"2018-07-31T19:57:57","date_gmt":"2018-07-31T19:57:57","guid":{"rendered":"http:\/\/dbtut.com\/?p=1199"},"modified":"2019-07-26T12:36:33","modified_gmt":"2019-07-26T12:36:33","slug":"how-to-create-an-updatable-view-with-check-option-in-postgresql","status":"publish","type":"post","link":"https:\/\/dbtut.com\/index.php\/2018\/07\/31\/how-to-create-an-updatable-view-with-check-option-in-postgresql\/","title":{"rendered":"How To Create an Updatable View WITH CHECK OPTION in PostgreSQL"},"content":{"rendered":"

I described creating an updateable view in the article “How To Create an Updatable View in PostgreSQL<\/a>“.<\/p>\n

In this article I will describe how to create an updateable view with WITH CHECK OPTION.<\/p>\n

In the previous article, although the filter in the view is NEWYORK, we could insert it as LONDON in the city name.<\/p>\n

This could create security vulnerability.<\/p>\n

If we create an updateable view with WITH CHECK OPTION, we only allow changes to the recordset that the view has access to.<\/p>\n

I recreate the updateable view as follows.<\/p>\n

CREATE OR REPLACE VIEW\u00a0 updateable_view_example AS\nSELECT name,city from table_example\nWHERE city='NEWYORK'\nWITH CHECK OPTION;<\/pre>\n
<\/p>\n
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.<\/p>\n
ERROR:\u00a0 new row violates check option for view “updateable_view_example”<\/span><\/em><\/span><\/p>\n
DETAIL:\u00a0 Failing row contains (Donnie Yen, Guangzhou).<\/span><\/em><\/span><\/p>\n
INSERT INTO updateable_view_example (name,city)\nVALUES('Donnie Yen','Guangzhou');<\/pre>\n
<\/p>\n
If you change the city name to NEWYORK, you can successfully complete the INSERT.<\/p>\n
INSERT INTO updateable_view_example (name,city)\nVALUES('Donnie Yen','NEWYORK');<\/pre>\n
<\/p>\n
You may want to look at the following articles about Views.<\/p>\n
“How To Create a View On PostgreSQL<\/a>“,<\/p>\n
“How To Create a Materialized View On PostgreSQL<\/a>“,<\/p>\n
“How To Create a Recursive View On PostgreSQL<\/a>“,<\/p>\n
“How To Create an Updatable View On PostgreSQL<\/a>”<\/p>\n\n
<\/div>\n
<\/path><\/svg><\/i> \"Loading\"<\/p>\n
<\/div>\n","protected":false},"excerpt":{"rendered":"
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. …<\/p>\n
<\/div>\n
<\/path><\/svg><\/i> \"Loading\"<\/p>\n
<\/div>\n","protected":false},"author":1,"featured_media":12910,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"om_disable_all_campaigns":false,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"_uf_show_specific_survey":0,"_uf_disable_surveys":false,"footnotes":""},"categories":[5],"tags":[1252,1254,1220,1253],"class_list":["post-1199","post","type-post","status-publish","format-standard","has-post-thumbnail","","category-postgres","tag-how-to-create-an-updateable-view-with-check-option-on-postgresql","tag-new-row-violates-check-option-for-view","tag-updateable-view","tag-with-check-option"],"aioseo_notices":[],"yoast_head":"\nHow To Create an Updatable View WITH CHECK OPTION in PostgreSQL - Database Tutorials<\/title>\n<meta name=\"description\" content=\"How To Create an Updateable View WITH CHECK OPTION On PostgreSQL\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/dbtut.com\/index.php\/2018\/07\/31\/how-to-create-an-updatable-view-with-check-option-in-postgresql\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"How To Create an Updatable View WITH CHECK OPTION in PostgreSQL - Database Tutorials\" \/>\n<meta property=\"og:description\" content=\"How To Create an Updateable View WITH CHECK OPTION On PostgreSQL\" \/>\n<meta property=\"og:url\" content=\"https:\/\/dbtut.com\/index.php\/2018\/07\/31\/how-to-create-an-updatable-view-with-check-option-in-postgresql\/\" \/>\n<meta property=\"og:site_name\" content=\"Database Tutorials\" \/>\n<meta property=\"article:published_time\" content=\"2018-07-31T19:57:57+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2019-07-26T12:36:33+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/dbtut.com\/wp-content\/uploads\/2018\/07\/Ads\u0131z-1.png\" \/>\n\t<meta property=\"og:image:width\" content=\"577\" \/>\n\t<meta property=\"og:image:height\" content=\"360\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/png\" \/>\n<meta name=\"author\" content=\"dbtut\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"dbtut\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"1 minute\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/dbtut.com\/index.php\/2018\/07\/31\/how-to-create-an-updatable-view-with-check-option-in-postgresql\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/dbtut.com\/index.php\/2018\/07\/31\/how-to-create-an-updatable-view-with-check-option-in-postgresql\/\"},\"author\":{\"name\":\"dbtut\",\"@id\":\"https:\/\/dbtut.com\/#\/schema\/person\/fc047c39e1e53dce28fc4253529ea408\"},\"headline\":\"How To Create an Updatable View WITH CHECK OPTION in PostgreSQL\",\"datePublished\":\"2018-07-31T19:57:57+00:00\",\"dateModified\":\"2019-07-26T12:36:33+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/dbtut.com\/index.php\/2018\/07\/31\/how-to-create-an-updatable-view-with-check-option-in-postgresql\/\"},\"wordCount\":205,\"commentCount\":0,\"publisher\":{\"@id\":\"https:\/\/dbtut.com\/#organization\"},\"image\":{\"@id\":\"https:\/\/dbtut.com\/index.php\/2018\/07\/31\/how-to-create-an-updatable-view-with-check-option-in-postgresql\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/dbtut.com\/wp-content\/uploads\/2018\/07\/Ads\u0131z-1.png\",\"keywords\":[\"How To Create an Updateable View WITH CHECK OPTION On PostgreSQL\",\"new row violates check option for view\",\"Updateable view\",\"WITH CHECK OPTION\"],\"articleSection\":[\"PostgreSQL\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/dbtut.com\/index.php\/2018\/07\/31\/how-to-create-an-updatable-view-with-check-option-in-postgresql\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/dbtut.com\/index.php\/2018\/07\/31\/how-to-create-an-updatable-view-with-check-option-in-postgresql\/\",\"url\":\"https:\/\/dbtut.com\/index.php\/2018\/07\/31\/how-to-create-an-updatable-view-with-check-option-in-postgresql\/\",\"name\":\"How To Create an Updatable View WITH CHECK OPTION in PostgreSQL - Database Tutorials\",\"isPartOf\":{\"@id\":\"https:\/\/dbtut.com\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/dbtut.com\/index.php\/2018\/07\/31\/how-to-create-an-updatable-view-with-check-option-in-postgresql\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/dbtut.com\/index.php\/2018\/07\/31\/how-to-create-an-updatable-view-with-check-option-in-postgresql\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/dbtut.com\/wp-content\/uploads\/2018\/07\/Ads\u0131z-1.png\",\"datePublished\":\"2018-07-31T19:57:57+00:00\",\"dateModified\":\"2019-07-26T12:36:33+00:00\",\"description\":\"How To Create an Updateable View WITH CHECK OPTION On PostgreSQL\",\"breadcrumb\":{\"@id\":\"https:\/\/dbtut.com\/index.php\/2018\/07\/31\/how-to-create-an-updatable-view-with-check-option-in-postgresql\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/dbtut.com\/index.php\/2018\/07\/31\/how-to-create-an-updatable-view-with-check-option-in-postgresql\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/dbtut.com\/index.php\/2018\/07\/31\/how-to-create-an-updatable-view-with-check-option-in-postgresql\/#primaryimage\",\"url\":\"https:\/\/dbtut.com\/wp-content\/uploads\/2018\/07\/Ads\u0131z-1.png\",\"contentUrl\":\"https:\/\/dbtut.com\/wp-content\/uploads\/2018\/07\/Ads\u0131z-1.png\",\"width\":577,\"height\":360},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/dbtut.com\/index.php\/2018\/07\/31\/how-to-create-an-updatable-view-with-check-option-in-postgresql\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/dbtut.com\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"How To Create an Updatable View WITH CHECK OPTION in PostgreSQL\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/dbtut.com\/#website\",\"url\":\"https:\/\/dbtut.com\/\",\"name\":\"Database Tutorials\",\"description\":\"MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux\",\"publisher\":{\"@id\":\"https:\/\/dbtut.com\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/dbtut.com\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Organization\",\"@id\":\"https:\/\/dbtut.com\/#organization\",\"name\":\"dbtut\",\"url\":\"https:\/\/dbtut.com\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/dbtut.com\/#\/schema\/logo\/image\/\",\"url\":\"https:\/\/dbtut.com\/wp-content\/uploads\/2021\/02\/dbtutlogo.jpg\",\"contentUrl\":\"https:\/\/dbtut.com\/wp-content\/uploads\/2021\/02\/dbtutlogo.jpg\",\"width\":223,\"height\":36,\"caption\":\"dbtut\"},\"image\":{\"@id\":\"https:\/\/dbtut.com\/#\/schema\/logo\/image\/\"}},{\"@type\":\"Person\",\"@id\":\"https:\/\/dbtut.com\/#\/schema\/person\/fc047c39e1e53dce28fc4253529ea408\",\"name\":\"dbtut\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/dbtut.com\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/c322c32021bf651d9e103b183963c479a9c9791ead0715f4348203496c39aa54?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/c322c32021bf651d9e103b183963c479a9c9791ead0715f4348203496c39aa54?s=96&d=mm&r=g\",\"caption\":\"dbtut\"},\"description\":\"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.\",\"sameAs\":[\"http:\/\/NurullahCAKIR\"],\"url\":\"https:\/\/dbtut.com\/index.php\/author\/dbtut\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"How To Create an Updatable View WITH CHECK OPTION in PostgreSQL - Database Tutorials","description":"How To Create an Updateable View WITH CHECK OPTION On PostgreSQL","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/dbtut.com\/index.php\/2018\/07\/31\/how-to-create-an-updatable-view-with-check-option-in-postgresql\/","og_locale":"en_US","og_type":"article","og_title":"How To Create an Updatable View WITH CHECK OPTION in PostgreSQL - Database Tutorials","og_description":"How To Create an Updateable View WITH CHECK OPTION On PostgreSQL","og_url":"https:\/\/dbtut.com\/index.php\/2018\/07\/31\/how-to-create-an-updatable-view-with-check-option-in-postgresql\/","og_site_name":"Database Tutorials","article_published_time":"2018-07-31T19:57:57+00:00","article_modified_time":"2019-07-26T12:36:33+00:00","og_image":[{"width":577,"height":360,"url":"https:\/\/dbtut.com\/wp-content\/uploads\/2018\/07\/Ads\u0131z-1.png","type":"image\/png"}],"author":"dbtut","twitter_card":"summary_large_image","twitter_misc":{"Written by":"dbtut","Est. reading time":"1 minute"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/dbtut.com\/index.php\/2018\/07\/31\/how-to-create-an-updatable-view-with-check-option-in-postgresql\/#article","isPartOf":{"@id":"https:\/\/dbtut.com\/index.php\/2018\/07\/31\/how-to-create-an-updatable-view-with-check-option-in-postgresql\/"},"author":{"name":"dbtut","@id":"https:\/\/dbtut.com\/#\/schema\/person\/fc047c39e1e53dce28fc4253529ea408"},"headline":"How To Create an Updatable View WITH CHECK OPTION in PostgreSQL","datePublished":"2018-07-31T19:57:57+00:00","dateModified":"2019-07-26T12:36:33+00:00","mainEntityOfPage":{"@id":"https:\/\/dbtut.com\/index.php\/2018\/07\/31\/how-to-create-an-updatable-view-with-check-option-in-postgresql\/"},"wordCount":205,"commentCount":0,"publisher":{"@id":"https:\/\/dbtut.com\/#organization"},"image":{"@id":"https:\/\/dbtut.com\/index.php\/2018\/07\/31\/how-to-create-an-updatable-view-with-check-option-in-postgresql\/#primaryimage"},"thumbnailUrl":"https:\/\/dbtut.com\/wp-content\/uploads\/2018\/07\/Ads\u0131z-1.png","keywords":["How To Create an Updateable View WITH CHECK OPTION On PostgreSQL","new row violates check option for view","Updateable view","WITH CHECK OPTION"],"articleSection":["PostgreSQL"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/dbtut.com\/index.php\/2018\/07\/31\/how-to-create-an-updatable-view-with-check-option-in-postgresql\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/dbtut.com\/index.php\/2018\/07\/31\/how-to-create-an-updatable-view-with-check-option-in-postgresql\/","url":"https:\/\/dbtut.com\/index.php\/2018\/07\/31\/how-to-create-an-updatable-view-with-check-option-in-postgresql\/","name":"How To Create an Updatable View WITH CHECK OPTION in PostgreSQL - Database Tutorials","isPartOf":{"@id":"https:\/\/dbtut.com\/#website"},"primaryImageOfPage":{"@id":"https:\/\/dbtut.com\/index.php\/2018\/07\/31\/how-to-create-an-updatable-view-with-check-option-in-postgresql\/#primaryimage"},"image":{"@id":"https:\/\/dbtut.com\/index.php\/2018\/07\/31\/how-to-create-an-updatable-view-with-check-option-in-postgresql\/#primaryimage"},"thumbnailUrl":"https:\/\/dbtut.com\/wp-content\/uploads\/2018\/07\/Ads\u0131z-1.png","datePublished":"2018-07-31T19:57:57+00:00","dateModified":"2019-07-26T12:36:33+00:00","description":"How To Create an Updateable View WITH CHECK OPTION On PostgreSQL","breadcrumb":{"@id":"https:\/\/dbtut.com\/index.php\/2018\/07\/31\/how-to-create-an-updatable-view-with-check-option-in-postgresql\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/dbtut.com\/index.php\/2018\/07\/31\/how-to-create-an-updatable-view-with-check-option-in-postgresql\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/dbtut.com\/index.php\/2018\/07\/31\/how-to-create-an-updatable-view-with-check-option-in-postgresql\/#primaryimage","url":"https:\/\/dbtut.com\/wp-content\/uploads\/2018\/07\/Ads\u0131z-1.png","contentUrl":"https:\/\/dbtut.com\/wp-content\/uploads\/2018\/07\/Ads\u0131z-1.png","width":577,"height":360},{"@type":"BreadcrumbList","@id":"https:\/\/dbtut.com\/index.php\/2018\/07\/31\/how-to-create-an-updatable-view-with-check-option-in-postgresql\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/dbtut.com\/"},{"@type":"ListItem","position":2,"name":"How To Create an Updatable View WITH CHECK OPTION in PostgreSQL"}]},{"@type":"WebSite","@id":"https:\/\/dbtut.com\/#website","url":"https:\/\/dbtut.com\/","name":"Database Tutorials","description":"MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux","publisher":{"@id":"https:\/\/dbtut.com\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/dbtut.com\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/dbtut.com\/#organization","name":"dbtut","url":"https:\/\/dbtut.com\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/dbtut.com\/#\/schema\/logo\/image\/","url":"https:\/\/dbtut.com\/wp-content\/uploads\/2021\/02\/dbtutlogo.jpg","contentUrl":"https:\/\/dbtut.com\/wp-content\/uploads\/2021\/02\/dbtutlogo.jpg","width":223,"height":36,"caption":"dbtut"},"image":{"@id":"https:\/\/dbtut.com\/#\/schema\/logo\/image\/"}},{"@type":"Person","@id":"https:\/\/dbtut.com\/#\/schema\/person\/fc047c39e1e53dce28fc4253529ea408","name":"dbtut","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/dbtut.com\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/c322c32021bf651d9e103b183963c479a9c9791ead0715f4348203496c39aa54?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/c322c32021bf651d9e103b183963c479a9c9791ead0715f4348203496c39aa54?s=96&d=mm&r=g","caption":"dbtut"},"description":"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.","sameAs":["http:\/\/NurullahCAKIR"],"url":"https:\/\/dbtut.com\/index.php\/author\/dbtut\/"}]}},"amp_enabled":true,"_links":{"self":[{"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/posts\/1199","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/comments?post=1199"}],"version-history":[{"count":0,"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/posts\/1199\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/media\/12910"}],"wp:attachment":[{"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/media?parent=1199"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/categories?post=1199"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/tags?post=1199"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}