{"id":1179,"date":"2018-07-30T20:02:56","date_gmt":"2018-07-30T20:02:56","guid":{"rendered":"http:\/\/dbtut.com\/?p=1179"},"modified":"2019-07-26T12:35:17","modified_gmt":"2019-07-26T12:35:17","slug":"how-to-create-an-updatable-view-in-postgresql","status":"publish","type":"post","link":"https:\/\/dbtut.com\/index.php\/2018\/07\/30\/how-to-create-an-updatable-view-in-postgresql\/","title":{"rendered":"How To Create an Updatable View in PostgreSQL"},"content":{"rendered":"

\n

In PostgreSQL, we can perform insert, update and delete operations on the relations specified in the view via views.<\/p>\n

Restrictions<\/h2>\n

Of course there are some restrictions. You can find these restrictions below.<\/p>\n

\u2022 GROUP BY, HAVING, LIMIT, OFFSET, DISTINCT, WITH, UNION, INTERSECT, and EXCEPT statements can not be used in the query in View.
\n\u2022 Window function, set-returning function or any aggregate function can not be used.<\/p>\n

A user who has the authority to update on View does not need to be authorized <\/span>on<\/span> the table.<\/span><\/p>\n

Only the owner of the view needs to be able to update the table.<\/p>\n

You can use updateable views in situations where you want certain users to update certain columns of certain tables.<\/p>\n

Let’s continue by making an example.<\/p>\n

I will use the table named table_example <\/span>that I created <\/span>in the article titled “How to Create a View in PostgreSQL<\/a>“.<\/span><\/p>\n

When we query the table, the following result is returned.<\/p>\n

select * from table_example;<\/pre>\n
<\/p>\n
We create an updateable view using the following script.<\/p>\n
CREATE VIEW\u00a0 updateable_view_example AS\nSELECT name from table_example\nWHERE city='NEWYORK';<\/pre>\n
<\/p>\n
We see the results by querying View as follows.<\/p>\n
SELECT * FROM updateable_view_example;<\/pre>\n
<\/p>\n
Let’s do an insert operation on the table via View.<\/p>\n
INSERT INTO updateable_view_example (name,city)\nVALUES('Jasonn Statham','London');<\/pre>\n
We will get an error like below. Because we did not define the city column in the view.<\/p>\n
column “city” of relation “updateable_view_example” does not exist.<\/em><\/span><\/p>\n
<\/p>\n
I recreate the View as follows.<\/p>\n
CREATE OR REPLACE VIEW updateable_view_example AS\nSELECT name,city from table_example\nWHERE city='NEWYORK';<\/pre>\n
<\/p>\n
Later, when we try to insert again, we will be able to perform the operation without any errors.<\/p>\n
But when we query the view with the Select statement, we will not be able to see the record we added.<\/p>\n
Because in the filter in the view, the city must be NEWYORK.<\/p>\n
But in the record we insert, the city column is LONDON.<\/p>\n
<\/p>\n
If you query the table with SELECT statement, you can see the record you added.<\/p>\n
SELECT * FROM table_example;<\/pre>\n
<\/p>\n
Let’s end the article by doing an update on the updateable view.<\/p>\n
UPDATE updateable_view_example SET city='OLDYORK';<\/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 WITH CHECK CONSTRAINT in PostgreSQL<\/a>”<\/p>\n\n
<\/div>\n
<\/path><\/svg><\/i> \"Loading\"<\/p>\n
<\/div>\n","protected":false},"excerpt":{"rendered":"
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. \u2022 GROUP BY, HAVING, LIMIT, OFFSET, DISTINCT, WITH, UNION, INTERSECT, and EXCEPT statements can not be used in the query in View. \u2022 …<\/p>\n
<\/div>\n
<\/path><\/svg><\/i> \"Loading\"<\/p>\n
<\/div>\n","protected":false},"author":1,"featured_media":12914,"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":[1251,1248,1249,1250,1220],"class_list":["post-1179","post","type-post","status-publish","format-standard","has-post-thumbnail","","category-postgres","tag-delete-from-view","tag-how-to-create-an-updateable-view-on-postgresql","tag-insert-into-view","tag-update-from-view","tag-updateable-view"],"aioseo_notices":[],"yoast_head":"\nHow To Create an Updatable View in PostgreSQL - Database Tutorials<\/title>\n<meta name=\"description\" content=\"How To Create an Updateable View 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\/30\/how-to-create-an-updatable-view-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 in PostgreSQL - Database Tutorials\" \/>\n<meta property=\"og:description\" content=\"How To Create an Updateable View On PostgreSQL\" \/>\n<meta property=\"og:url\" content=\"https:\/\/dbtut.com\/index.php\/2018\/07\/30\/how-to-create-an-updatable-view-in-postgresql\/\" \/>\n<meta property=\"og:site_name\" content=\"Database Tutorials\" \/>\n<meta property=\"article:published_time\" content=\"2018-07-30T20:02:56+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2019-07-26T12:35:17+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/dbtut.com\/wp-content\/uploads\/2018\/07\/Ads\u0131z-2.png\" \/>\n\t<meta property=\"og:image:width\" content=\"577\" \/>\n\t<meta property=\"og:image:height\" content=\"348\" \/>\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=\"2 minutes\" \/>\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\/30\/how-to-create-an-updatable-view-in-postgresql\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/dbtut.com\/index.php\/2018\/07\/30\/how-to-create-an-updatable-view-in-postgresql\/\"},\"author\":{\"name\":\"dbtut\",\"@id\":\"https:\/\/dbtut.com\/#\/schema\/person\/fc047c39e1e53dce28fc4253529ea408\"},\"headline\":\"How To Create an Updatable View in PostgreSQL\",\"datePublished\":\"2018-07-30T20:02:56+00:00\",\"dateModified\":\"2019-07-26T12:35:17+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/dbtut.com\/index.php\/2018\/07\/30\/how-to-create-an-updatable-view-in-postgresql\/\"},\"wordCount\":362,\"commentCount\":0,\"publisher\":{\"@id\":\"https:\/\/dbtut.com\/#organization\"},\"image\":{\"@id\":\"https:\/\/dbtut.com\/index.php\/2018\/07\/30\/how-to-create-an-updatable-view-in-postgresql\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/dbtut.com\/wp-content\/uploads\/2018\/07\/Ads\u0131z-2.png\",\"keywords\":[\"Delete From View\",\"How To Create an Updateable View On PostgreSQL\",\"Insert Into View\",\"Update From View\",\"Updateable view\"],\"articleSection\":[\"PostgreSQL\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/dbtut.com\/index.php\/2018\/07\/30\/how-to-create-an-updatable-view-in-postgresql\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/dbtut.com\/index.php\/2018\/07\/30\/how-to-create-an-updatable-view-in-postgresql\/\",\"url\":\"https:\/\/dbtut.com\/index.php\/2018\/07\/30\/how-to-create-an-updatable-view-in-postgresql\/\",\"name\":\"How To Create an Updatable View in PostgreSQL - Database Tutorials\",\"isPartOf\":{\"@id\":\"https:\/\/dbtut.com\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/dbtut.com\/index.php\/2018\/07\/30\/how-to-create-an-updatable-view-in-postgresql\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/dbtut.com\/index.php\/2018\/07\/30\/how-to-create-an-updatable-view-in-postgresql\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/dbtut.com\/wp-content\/uploads\/2018\/07\/Ads\u0131z-2.png\",\"datePublished\":\"2018-07-30T20:02:56+00:00\",\"dateModified\":\"2019-07-26T12:35:17+00:00\",\"description\":\"How To Create an Updateable View On PostgreSQL\",\"breadcrumb\":{\"@id\":\"https:\/\/dbtut.com\/index.php\/2018\/07\/30\/how-to-create-an-updatable-view-in-postgresql\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/dbtut.com\/index.php\/2018\/07\/30\/how-to-create-an-updatable-view-in-postgresql\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/dbtut.com\/index.php\/2018\/07\/30\/how-to-create-an-updatable-view-in-postgresql\/#primaryimage\",\"url\":\"https:\/\/dbtut.com\/wp-content\/uploads\/2018\/07\/Ads\u0131z-2.png\",\"contentUrl\":\"https:\/\/dbtut.com\/wp-content\/uploads\/2018\/07\/Ads\u0131z-2.png\",\"width\":577,\"height\":348},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/dbtut.com\/index.php\/2018\/07\/30\/how-to-create-an-updatable-view-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 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 in PostgreSQL - Database Tutorials","description":"How To Create an Updateable View 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\/30\/how-to-create-an-updatable-view-in-postgresql\/","og_locale":"en_US","og_type":"article","og_title":"How To Create an Updatable View in PostgreSQL - Database Tutorials","og_description":"How To Create an Updateable View On PostgreSQL","og_url":"https:\/\/dbtut.com\/index.php\/2018\/07\/30\/how-to-create-an-updatable-view-in-postgresql\/","og_site_name":"Database Tutorials","article_published_time":"2018-07-30T20:02:56+00:00","article_modified_time":"2019-07-26T12:35:17+00:00","og_image":[{"width":577,"height":348,"url":"https:\/\/dbtut.com\/wp-content\/uploads\/2018\/07\/Ads\u0131z-2.png","type":"image\/png"}],"author":"dbtut","twitter_card":"summary_large_image","twitter_misc":{"Written by":"dbtut","Est. reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/dbtut.com\/index.php\/2018\/07\/30\/how-to-create-an-updatable-view-in-postgresql\/#article","isPartOf":{"@id":"https:\/\/dbtut.com\/index.php\/2018\/07\/30\/how-to-create-an-updatable-view-in-postgresql\/"},"author":{"name":"dbtut","@id":"https:\/\/dbtut.com\/#\/schema\/person\/fc047c39e1e53dce28fc4253529ea408"},"headline":"How To Create an Updatable View in PostgreSQL","datePublished":"2018-07-30T20:02:56+00:00","dateModified":"2019-07-26T12:35:17+00:00","mainEntityOfPage":{"@id":"https:\/\/dbtut.com\/index.php\/2018\/07\/30\/how-to-create-an-updatable-view-in-postgresql\/"},"wordCount":362,"commentCount":0,"publisher":{"@id":"https:\/\/dbtut.com\/#organization"},"image":{"@id":"https:\/\/dbtut.com\/index.php\/2018\/07\/30\/how-to-create-an-updatable-view-in-postgresql\/#primaryimage"},"thumbnailUrl":"https:\/\/dbtut.com\/wp-content\/uploads\/2018\/07\/Ads\u0131z-2.png","keywords":["Delete From View","How To Create an Updateable View On PostgreSQL","Insert Into View","Update From View","Updateable view"],"articleSection":["PostgreSQL"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/dbtut.com\/index.php\/2018\/07\/30\/how-to-create-an-updatable-view-in-postgresql\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/dbtut.com\/index.php\/2018\/07\/30\/how-to-create-an-updatable-view-in-postgresql\/","url":"https:\/\/dbtut.com\/index.php\/2018\/07\/30\/how-to-create-an-updatable-view-in-postgresql\/","name":"How To Create an Updatable View in PostgreSQL - Database Tutorials","isPartOf":{"@id":"https:\/\/dbtut.com\/#website"},"primaryImageOfPage":{"@id":"https:\/\/dbtut.com\/index.php\/2018\/07\/30\/how-to-create-an-updatable-view-in-postgresql\/#primaryimage"},"image":{"@id":"https:\/\/dbtut.com\/index.php\/2018\/07\/30\/how-to-create-an-updatable-view-in-postgresql\/#primaryimage"},"thumbnailUrl":"https:\/\/dbtut.com\/wp-content\/uploads\/2018\/07\/Ads\u0131z-2.png","datePublished":"2018-07-30T20:02:56+00:00","dateModified":"2019-07-26T12:35:17+00:00","description":"How To Create an Updateable View On PostgreSQL","breadcrumb":{"@id":"https:\/\/dbtut.com\/index.php\/2018\/07\/30\/how-to-create-an-updatable-view-in-postgresql\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/dbtut.com\/index.php\/2018\/07\/30\/how-to-create-an-updatable-view-in-postgresql\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/dbtut.com\/index.php\/2018\/07\/30\/how-to-create-an-updatable-view-in-postgresql\/#primaryimage","url":"https:\/\/dbtut.com\/wp-content\/uploads\/2018\/07\/Ads\u0131z-2.png","contentUrl":"https:\/\/dbtut.com\/wp-content\/uploads\/2018\/07\/Ads\u0131z-2.png","width":577,"height":348},{"@type":"BreadcrumbList","@id":"https:\/\/dbtut.com\/index.php\/2018\/07\/30\/how-to-create-an-updatable-view-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 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\/1179","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=1179"}],"version-history":[{"count":0,"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/posts\/1179\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/media\/12914"}],"wp:attachment":[{"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/media?parent=1179"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/categories?post=1179"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/tags?post=1179"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}