{"id":54555,"date":"2023-05-27T20:52:03","date_gmt":"2023-05-27T20:52:03","guid":{"rendered":"https:\/\/dbtut.com\/?p=54555"},"modified":"2023-05-27T20:52:48","modified_gmt":"2023-05-27T20:52:48","slug":"postgresql-upsert","status":"publish","type":"post","link":"https:\/\/dbtut.com\/index.php\/2023\/05\/27\/postgresql-upsert\/","title":{"rendered":"PostgreSQL Upsert"},"content":{"rendered":"

In today’s article, we will be cover about the Upsert feature that came into our lives with PostgreSQL 9.5.<\/p>\n

Upsert makes an insert if there is no record in the table, and if there is, it uses the specified column to update it.<\/p>\n

While doing this, your table must have at least one unique value.<\/p>\n

General use:<\/h3>\n
INSERT INTO Table_name (column1 ,column2,\u2026.) VALUES (VALUE1,VALUE2,\u2026.)\r\nON CONFLICT DO NOTHING\r\nor \r\nON CONFLICT (column_name) DO UPDATE<\/pre>\n
The part that starts with insert above is the insert clause we use to add data.<\/p>\n
ON CONFLICT DO NOTHING<\/h3>\n
If it is the same as the record in the table to be added, it means making a transaction.<\/p>\n
ON CONFLICT (column_name) DO UPDATE<\/h3>\n
The data to be added exists in the table, but it is used if it is desired to be changed.<\/p>\n
Let’s reinforce it with practice.<\/p>\n
First, let’s create our aupsert table in our table.<\/p>\n
CREATE TABLE  aupsert (k1 int unique ,k2 char(10),k3 char(10))\r\n<\/pre>\n
Let’s add our data to our table.<\/p>\n
INSERT INTO aupsert VALUES (1,'Faruk','ERDEM');\r\nINSERT INTO aupsert VALUES (2,'Ufuk','ERDEM');\r\nINSERT INTO aupsert VALUES (3,'Utku','ERDEM')<\/pre>\n
After adding our data, let’s query our table.<\/p>\n
<\/p>\n
Let’s see how it will behave in the ON CONFLICT DO NOTHING parameter by adding a record with the same name to our table.<\/p>\n
<\/p>\n
Above, we see that it gives output as “INSERT 0 0”. This means that data cannot be added to our table.<\/p>\n
<\/p>\n
We found that our data was not appended. Let’s try to add the same data using the ON CONFLICT (column_name) DO UPDATE command.<\/p>\n
INSERT INTO aupsert VALUES (3,'Utku','ER')\r\nON CONFLICT (k1)\r\nDO    UPDATE SET k2 = EXCLUDED.k2 , k3 = excluded.k3 ;<\/pre>\n
We specify the unique column with the ON CONFLICT(k1) parameter.<\/p>\n
In the DO UPDATE SET command, we write the columns to be changed.<\/p>\n
<\/p>\n
In this example, our output has changed to 0 1, let’s check our data by pulling a select from the table.<\/p>\n
<\/p>\n
As seen above, we see that our ERDEM record in column k3 has changed.<\/p>\n
You can use the example below to use upsert between different tables.<\/p>\n
First, let’s create our table and add our data.<\/p>\n
CREATE TABLE aupsert2 (k1 int unique ,k2 char(10),k3 char(10))\r\nINSERT INTO aupsert2 VALUES (3,'Utku','ERDEM');\r\nINSERT INTO aupsert2 VALUES (4,'Ahmet','ASLAN');\r\nINSERT INTO aupsert2 VALUES (5,'Mehmet','KIR');<\/pre>\n
After adding our data, let’s transfer the data in the aupsert2 table to the aupsert table.<\/p>\n
First, let’s view our data in our table.<\/p>\n
<\/p>\n
Let’s list the data in the aupsert2 table.<\/p>\n
<\/p>\n
Let’s add the data that is different between our first table and our second table to our first table with the help of the following command.<\/p>\n
INSERT INTO aupsert(k1,k2,k3)\r\nSELECT k1,k2,k3\r\nFROM aupsert2\r\nON CONFLICT (k1)\r\nDO\r\n   UPDATE SET k2 = EXCLUDED.k2 , k3 = excluded.k3 ;<\/pre>\n
<\/p>\n
As it can be seen above, since there are no 4th and 5th records, it has been added to our aupsert table and in the 3rd record, “ER” in column k3 has been updated as “ERDEM”.<\/p>\n
With the example above, the insert and update process were carried out together.<\/p>\n
<\/div>\n
<\/path><\/svg><\/i> \"Loading\"<\/p>\n
<\/div>\n","protected":false},"excerpt":{"rendered":"
In today’s article, we will be cover about the Upsert feature that came into our lives with PostgreSQL 9.5. Upsert makes an insert if there is no record in the table, and if there is, it uses the specified column to update it. While doing this, your table must have at least one unique value. …<\/p>\n
<\/div>\n
<\/path><\/svg><\/i> \"Loading\"<\/p>\n
<\/div>\n","protected":false},"author":366,"featured_media":54565,"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":[],"class_list":["post-54555","post","type-post","status-publish","format-standard","has-post-thumbnail","","category-postgres"],"aioseo_notices":[],"yoast_head":"\nPostgreSQL Upsert - Database Tutorials<\/title>\n<meta name=\"description\" content=\"In today's article, we will be cover about the Upsert feature that came into our lives with PostgreSQL 9.5.\" \/>\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\/2023\/05\/27\/postgresql-upsert\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"PostgreSQL Upsert - Database Tutorials\" \/>\n<meta property=\"og:description\" content=\"In today's article, we will be cover about the Upsert feature that came into our lives with PostgreSQL 9.5.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/dbtut.com\/index.php\/2023\/05\/27\/postgresql-upsert\/\" \/>\n<meta property=\"og:site_name\" content=\"Database Tutorials\" \/>\n<meta property=\"article:published_time\" content=\"2023-05-27T20:52:03+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2023-05-27T20:52:48+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/dbtut.com\/wp-content\/uploads\/2023\/05\/Ekran-goruntusu-2023-05-27-235041.png\" \/>\n\t<meta property=\"og:image:width\" content=\"625\" \/>\n\t<meta property=\"og:image:height\" content=\"331\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/png\" \/>\n<meta name=\"author\" content=\"Faruk Erdem\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Faruk Erdem\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"4 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/dbtut.com\/index.php\/2023\/05\/27\/postgresql-upsert\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/dbtut.com\/index.php\/2023\/05\/27\/postgresql-upsert\/\"},\"author\":{\"name\":\"Faruk Erdem\",\"@id\":\"https:\/\/dbtut.com\/#\/schema\/person\/a7dfc5684c116e536b4e93ee214ccbfb\"},\"headline\":\"PostgreSQL Upsert\",\"datePublished\":\"2023-05-27T20:52:03+00:00\",\"dateModified\":\"2023-05-27T20:52:48+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/dbtut.com\/index.php\/2023\/05\/27\/postgresql-upsert\/\"},\"wordCount\":404,\"commentCount\":0,\"publisher\":{\"@id\":\"https:\/\/dbtut.com\/#organization\"},\"image\":{\"@id\":\"https:\/\/dbtut.com\/index.php\/2023\/05\/27\/postgresql-upsert\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/dbtut.com\/wp-content\/uploads\/2023\/05\/Ekran-goruntusu-2023-05-27-235041.png\",\"articleSection\":[\"PostgreSQL\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/dbtut.com\/index.php\/2023\/05\/27\/postgresql-upsert\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/dbtut.com\/index.php\/2023\/05\/27\/postgresql-upsert\/\",\"url\":\"https:\/\/dbtut.com\/index.php\/2023\/05\/27\/postgresql-upsert\/\",\"name\":\"PostgreSQL Upsert - Database Tutorials\",\"isPartOf\":{\"@id\":\"https:\/\/dbtut.com\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/dbtut.com\/index.php\/2023\/05\/27\/postgresql-upsert\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/dbtut.com\/index.php\/2023\/05\/27\/postgresql-upsert\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/dbtut.com\/wp-content\/uploads\/2023\/05\/Ekran-goruntusu-2023-05-27-235041.png\",\"datePublished\":\"2023-05-27T20:52:03+00:00\",\"dateModified\":\"2023-05-27T20:52:48+00:00\",\"description\":\"In today's article, we will be cover about the Upsert feature that came into our lives with PostgreSQL 9.5.\",\"breadcrumb\":{\"@id\":\"https:\/\/dbtut.com\/index.php\/2023\/05\/27\/postgresql-upsert\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/dbtut.com\/index.php\/2023\/05\/27\/postgresql-upsert\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/dbtut.com\/index.php\/2023\/05\/27\/postgresql-upsert\/#primaryimage\",\"url\":\"https:\/\/dbtut.com\/wp-content\/uploads\/2023\/05\/Ekran-goruntusu-2023-05-27-235041.png\",\"contentUrl\":\"https:\/\/dbtut.com\/wp-content\/uploads\/2023\/05\/Ekran-goruntusu-2023-05-27-235041.png\",\"width\":625,\"height\":331},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/dbtut.com\/index.php\/2023\/05\/27\/postgresql-upsert\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/dbtut.com\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"PostgreSQL Upsert\"}]},{\"@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\/a7dfc5684c116e536b4e93ee214ccbfb\",\"name\":\"Faruk Erdem\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/dbtut.com\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/ad1e61fb5a7c9a590e765f7cad8f2dc8332090f1ceb9a5ee2aa95c69213f0c50?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/ad1e61fb5a7c9a590e765f7cad8f2dc8332090f1ceb9a5ee2aa95c69213f0c50?s=96&d=mm&r=g\",\"caption\":\"Faruk Erdem\"},\"url\":\"https:\/\/dbtut.com\/index.php\/author\/farukerdem\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"PostgreSQL Upsert - Database Tutorials","description":"In today's article, we will be cover about the Upsert feature that came into our lives with PostgreSQL 9.5.","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\/2023\/05\/27\/postgresql-upsert\/","og_locale":"en_US","og_type":"article","og_title":"PostgreSQL Upsert - Database Tutorials","og_description":"In today's article, we will be cover about the Upsert feature that came into our lives with PostgreSQL 9.5.","og_url":"https:\/\/dbtut.com\/index.php\/2023\/05\/27\/postgresql-upsert\/","og_site_name":"Database Tutorials","article_published_time":"2023-05-27T20:52:03+00:00","article_modified_time":"2023-05-27T20:52:48+00:00","og_image":[{"width":625,"height":331,"url":"https:\/\/dbtut.com\/wp-content\/uploads\/2023\/05\/Ekran-goruntusu-2023-05-27-235041.png","type":"image\/png"}],"author":"Faruk Erdem","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Faruk Erdem","Est. reading time":"4 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/dbtut.com\/index.php\/2023\/05\/27\/postgresql-upsert\/#article","isPartOf":{"@id":"https:\/\/dbtut.com\/index.php\/2023\/05\/27\/postgresql-upsert\/"},"author":{"name":"Faruk Erdem","@id":"https:\/\/dbtut.com\/#\/schema\/person\/a7dfc5684c116e536b4e93ee214ccbfb"},"headline":"PostgreSQL Upsert","datePublished":"2023-05-27T20:52:03+00:00","dateModified":"2023-05-27T20:52:48+00:00","mainEntityOfPage":{"@id":"https:\/\/dbtut.com\/index.php\/2023\/05\/27\/postgresql-upsert\/"},"wordCount":404,"commentCount":0,"publisher":{"@id":"https:\/\/dbtut.com\/#organization"},"image":{"@id":"https:\/\/dbtut.com\/index.php\/2023\/05\/27\/postgresql-upsert\/#primaryimage"},"thumbnailUrl":"https:\/\/dbtut.com\/wp-content\/uploads\/2023\/05\/Ekran-goruntusu-2023-05-27-235041.png","articleSection":["PostgreSQL"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/dbtut.com\/index.php\/2023\/05\/27\/postgresql-upsert\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/dbtut.com\/index.php\/2023\/05\/27\/postgresql-upsert\/","url":"https:\/\/dbtut.com\/index.php\/2023\/05\/27\/postgresql-upsert\/","name":"PostgreSQL Upsert - Database Tutorials","isPartOf":{"@id":"https:\/\/dbtut.com\/#website"},"primaryImageOfPage":{"@id":"https:\/\/dbtut.com\/index.php\/2023\/05\/27\/postgresql-upsert\/#primaryimage"},"image":{"@id":"https:\/\/dbtut.com\/index.php\/2023\/05\/27\/postgresql-upsert\/#primaryimage"},"thumbnailUrl":"https:\/\/dbtut.com\/wp-content\/uploads\/2023\/05\/Ekran-goruntusu-2023-05-27-235041.png","datePublished":"2023-05-27T20:52:03+00:00","dateModified":"2023-05-27T20:52:48+00:00","description":"In today's article, we will be cover about the Upsert feature that came into our lives with PostgreSQL 9.5.","breadcrumb":{"@id":"https:\/\/dbtut.com\/index.php\/2023\/05\/27\/postgresql-upsert\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/dbtut.com\/index.php\/2023\/05\/27\/postgresql-upsert\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/dbtut.com\/index.php\/2023\/05\/27\/postgresql-upsert\/#primaryimage","url":"https:\/\/dbtut.com\/wp-content\/uploads\/2023\/05\/Ekran-goruntusu-2023-05-27-235041.png","contentUrl":"https:\/\/dbtut.com\/wp-content\/uploads\/2023\/05\/Ekran-goruntusu-2023-05-27-235041.png","width":625,"height":331},{"@type":"BreadcrumbList","@id":"https:\/\/dbtut.com\/index.php\/2023\/05\/27\/postgresql-upsert\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/dbtut.com\/"},{"@type":"ListItem","position":2,"name":"PostgreSQL Upsert"}]},{"@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\/a7dfc5684c116e536b4e93ee214ccbfb","name":"Faruk Erdem","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/dbtut.com\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/ad1e61fb5a7c9a590e765f7cad8f2dc8332090f1ceb9a5ee2aa95c69213f0c50?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/ad1e61fb5a7c9a590e765f7cad8f2dc8332090f1ceb9a5ee2aa95c69213f0c50?s=96&d=mm&r=g","caption":"Faruk Erdem"},"url":"https:\/\/dbtut.com\/index.php\/author\/farukerdem\/"}]}},"amp_enabled":true,"_links":{"self":[{"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/posts\/54555","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\/366"}],"replies":[{"embeddable":true,"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/comments?post=54555"}],"version-history":[{"count":1,"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/posts\/54555\/revisions"}],"predecessor-version":[{"id":54566,"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/posts\/54555\/revisions\/54566"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/media\/54565"}],"wp:attachment":[{"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/media?parent=54555"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/categories?post=54555"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/tags?post=54555"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}