{"id":14164,"date":"2022-08-30T16:39:43","date_gmt":"2022-08-30T16:39:43","guid":{"rendered":"https:\/\/blog.cloudthat.com\/?p=14164"},"modified":"2024-06-25T10:54:48","modified_gmt":"2024-06-25T10:54:48","slug":"introduction-to-postgresql-and-postgresql-cte-upsert","status":"publish","type":"blog","link":"https:\/\/www.cloudthat.com\/resources\/blog\/introduction-to-postgresql-and-postgresql-cte-upsert","title":{"rendered":"Introduction to PostgreSQL and PostgreSQL CTE, Upsert"},"content":{"rendered":"<p><span style=\"color: #000000;\">This blog will discuss PostgreSQL and the uniqueness of PostgreSQL as compared to other open-source SQL software. Let us explore PostgreSQL Common Table Expressions (CTE), PostgreSQL Upsert (Update+Insert), and much more.<\/span><\/p>\n<h2><strong><span style=\"color: #000080;\">Introduction to PostgreSQL<\/span><\/strong><\/h2>\n<p><span style=\"color: #000000;\">PostgreSQL is a relational database management system that is sophisticated, enterprise-class, and open-source. PostgreSQL supports both relational and non-relational data querying. It is a robust database that the open-source community has developed for more than two decades.<\/span><\/p>\n<p><span style=\"color: #000000;\">Many online apps, mobile, and analytics applications use PostgreSQL as their primary database.<\/span><\/p>\n<h2><strong><span style=\"color: #000080;\">Top features of PostgreSQL<\/span><\/strong><\/h2>\n<p><span style=\"color: #000000;\">Many advanced features are available in PostgreSQL that are not available in other enterprise-class database management systems, such as:<\/span><\/p>\n<ul>\n<li><span style=\"color: #000000;\">Types that you declare<\/span><\/li>\n<li><span style=\"color: #000000;\">Inheritance in tables is a concept that has been<\/span><\/li>\n<li><span style=\"color: #000000;\">Sophisticated locking mechanism<\/span><\/li>\n<li><span style=\"color: #000000;\">Reference integrity of foreign keys<\/span><\/li>\n<li><span style=\"color: #000000;\">Subquery, views, rules<\/span><\/li>\n<li><span style=\"color: #000000;\">Transactions that are linked together (save points)<\/span><\/li>\n<li><span style=\"color: #000000;\">Extensibility is a key feature of PostgreSQL. We can create our data types, index types, functional languages, and other features in PostgreSQL.<\/span><\/li>\n<li><span style=\"color: #000000;\">If there is something about the system we do not like, we may always create a custom plugin to improve it to match your needs, such as adding a new optimizer.<\/span><\/li>\n<\/ul>\n<h2><strong><span style=\"color: #000080;\">Uniqueness of PostgreSQL<\/span><\/strong><\/h2>\n<p><span style=\"color: #000000;\">PostgreSQL is an object-relational database. It offers an advantage over MySQL, MariaDB, and Firebird, all open-source SQL databases. It supports many data types as described below:<\/span><\/p>\n<ul>\n<li><span style=\"color: #000000;\"><strong>JSON support:<\/strong> The JSON support in PostgreSQL allows you to use a SQL database without a schema. It is beneficial when the data structure requires some flexibility due to ongoing development or when the data fields that the data object will include are unknown. The JSON data type enforces proper JSON, allowing you to query and manipulate the data with the specific JSON operators and functions included in PostgreSQL.<\/span><\/li>\n<li><span style=\"color: #000000;\"><strong>Geometric Data: <\/strong>Geo-data is quickly becoming a requirement for many applications. Geometric data types such as points, lines, circles, and polygons have long been supported by PostgreSQL. One of these is the PATH data type. A path is a series of points that might be open (the starting and end points are not connected) or closed (the beginning and end points are connected) (the beginning and end points are connected).<\/span><\/li>\n<li><span style=\"color: #000000;\"><strong>Network Address:<\/strong> Different network address types can be stored in PostgreSQL. The CIDR data type (Classless Internet Domain Routing) follows the IPv4 and IPv6 network address conventions. The INET data type, which is used for IPv4 and IPv6 hosts where the subnet is optional, is also accessible for network address storage. The MACADDR data type can be used to store MAC addresses.<\/span><\/li>\n<\/ul>\n<h2><span style=\"color: #000080;\"><strong>PostgreSQL CTE<\/strong><\/span><\/h2>\n<p><span style=\"color: #000000;\">The CTE (Common Table Expression) is a temporary result set in PostgreSQL that the user can reference within another SQL operation such as SELECT, INSERT, UPDATE, or DELETE. CTEs are transitory because they exist only while the query is being executed. In PostgreSQL, CTEs are commonly used to simplify complex joins and subqueries.<\/span><\/p>\n<p><span style=\"color: #000000;\"><strong>Syntax:<\/strong><\/span><\/p>\n<pre class=\"theme:dark-terminal nums:false nums-toggle:false lang:default decode:true\">With CTE_Name (Column_list) as (\r\nCTE_query_statement\r\n)\r\nStatement;<\/pre>\n<p><span style=\"color: #000000;\">The CTE&#8217;s name comes first, followed by an optional column list.<\/span><\/p>\n<p><span style=\"color: #000000;\">Second, specify a query that returns a result set inside the WITH clause&#8217;s body. If you do not mention the column list following the CTE name, the CTE query definition&#8217;s select list will be used as the CTE&#8217;s column list.<\/span><\/p>\n<p><span style=\"color: #000000;\">Third, treat the CTE like a table or view in the SELECT, INSERT, UPDATE, or DELETE statement.<\/span><\/p>\n<p><span style=\"color: #000000;\"><strong>Example:<\/strong><\/span><\/p>\n<pre class=\"theme:dark-terminal nums:false nums-toggle:false lang:default decode:true\">With cte_insert as (\r\nInsert into Students(id, name) values (1,\u2019Ravi\u2019) returning id\r\n) \r\nSelect * from Students where id = (select id from cte_insert)<\/pre>\n<h2><strong><span lang=\"EN-IN\" style=\"color: #000080;\">Executing multiple queries using CTE<\/span><\/strong><\/h2>\n<pre class=\"theme:dark-terminal nums:false nums-toggle:false lang:default decode:true\">With cte_std as (\r\nInsert into Students(id, name, courseid) values (2,\u2019Hari\u2019,101) returning id,courseid\r\n), cte_course_insert as (\r\nInsert into Course(sid, cid) values ((select id from cte_std),(select coursed from cte_std))\r\n) \r\nSelect * from Students join Course on Students.id=Course.sid;<\/pre>\n<p><span style=\"color: #000000;\">In this above query, we insert data into different tables in a single query without using any subqueries, breaking complex things into simpler ones.<\/span><\/p>\n<h2><strong><span style=\"color: #000080;\">PostgreSQL UPSERT<\/span><\/strong><\/h2>\n<p><span style=\"color: #000000;\">The phrase upsert is also used as a merge in relational databases. If a row already exists in the table, PostgreSQL will update it; otherwise, it will insert the new row. Hence the action is known as upsert (update or insert).<\/span><\/p>\n<p><span style=\"color: #000000;\"><strong>Syntax:<\/strong><\/span><\/p>\n<pre class=\"theme:dark-terminal nums:false nums-toggle:false lang:default decode:true\">Insert into table_name(col1, col2) \r\nValues (val1, val2,)\r\nOn conflict &lt;colume_name\/where_clause&gt;  action;<\/pre>\n<p><span style=\"color: #000000;\">One of the following <strong>actions<\/strong> could be taken:<\/span><\/p>\n<p><span style=\"color: #000000;\">DO NOTHING \u2014 If the row already exists in the table, do nothing.<\/span><\/p>\n<p><span style=\"color: #000000;\">WHERE condition \u2013 change some fields in the table. DO UPDATE SET column 1 = value 1<\/span><\/p>\n<p><strong><span style=\"color: #000000;\">Example:<\/span><\/strong><\/p>\n<pre class=\"theme:dark-terminal nums:false nums-toggle:false lang:default decode:true\">Create a Table as shown below:\r\ncreate table doctor (\r\nid integer PRIMARY KEY,\r\n name varchar(100), \r\nactive varchar(100)\r\n);\r\n<\/pre>\n<p>Now, insert data into it:<\/p>\n<pre class=\"theme:dark-terminal nums:false nums-toggle:false lang:default decode:true\">insert into doctor (id, name, active) values (1, 'doctor1', 'true');\r\n-&gt;select * from doctor;\r\n<\/pre>\n<p><a href=\"https:\/\/d1f7lmxeo98xps.cloudfront.net\/resources\/wp-content\/uploads\/2022\/11\/postgresql1.png\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-14167\" src=\"https:\/\/d1f7lmxeo98xps.cloudfront.net\/resources\/wp-content\/uploads\/2022\/11\/postgresql1.png\" alt=\"postgresql\" width=\"295\" height=\"126\" \/><\/a><\/p>\n<p>Let us check if doctor1 is present then we make him inactive, if he is not there in the table then we will insert a new record and set him too inactive.<\/p>\n<pre class=\"theme:dark-terminal nums:false nums-toggle:false lang:default decode:true\">insert into doctor(id, name, active) \r\n values (1,\u2019doctor1\u2019 ,'false') \r\n on conflict (id) \r\n do update set active = 'false'\r\n-&gt;select * from doctor;<\/pre>\n<p><a href=\"https:\/\/d1f7lmxeo98xps.cloudfront.net\/resources\/wp-content\/uploads\/2022\/11\/postgresql2.png\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-14168\" src=\"https:\/\/d1f7lmxeo98xps.cloudfront.net\/resources\/wp-content\/uploads\/2022\/11\/postgresql2.png\" alt=\"postgresql\" width=\"280\" height=\"138\" \/><\/a><\/p>\n<p><span style=\"color: #000000;\">In the above query, as id=1 already exists, only the update query worked, or else the insert query has worked. The column name we give in on conflict should be either unique or a primary key constraint.<\/span><\/p>\n<h2><strong><span style=\"color: #000080;\">Conclusion<\/span><\/strong><\/h2>\n<p><span style=\"color: #000000;\">Today we have gone through the Postgre SQL features and its unique characteristics which made Postgre SQL more popular among other\u00a0open-source SQL software. We have used temporary tables i.e CTEs, which make our query more optimized.<\/span><\/p>\n<h2><strong><span style=\"color: #000080;\">About CloudThat<\/span><\/strong><\/h2>\n<p><span style=\"color: #000000;\"><a href=\"https:\/\/www.cloudthat.com\/\" target=\"_blank\" rel=\"noopener\"><strong>CloudThat<\/strong><\/a>\u00a0is\u00a0the official AWS (Amazon Web Services) Advanced Consulting Partner, Microsoft Gold Partner, Google Cloud Partner, and Training Partner helping people develop knowledge of the cloud and help their businesses aim for higher goals using best-in-industry cloud computing practices and expertise. We are on a mission to build\u00a0a robust\u00a0cloud computing ecosystem by disseminating\u00a0knowledge on technological intricacies within the cloud space.<span class=\"TextRun BCX0 SCXP93070984\" lang=\"EN-IN\" xml:lang=\"EN-IN\"><span class=\"NormalTextRun BCX0 SCXP93070984\"><span class=\"TextRun BCX0 SCXP59000031\" lang=\"EN-IN\" xml:lang=\"EN-IN\"><span class=\"NormalTextRun BCX0 SCXP59000031\">\u00a0<\/span><\/span><span class=\"TextRun BCX0 SCXP59000031\" lang=\"EN-IN\" xml:lang=\"EN-IN\"><span class=\"NormalTextRun BCX0 SCXP59000031\"><span class=\"EOP SCXP258354852 BCX0\"><span class=\"EOP SCXP66056781 BCX0\"><span class=\"EOP SCXP242272637 BCX0\"><span class=\"TextRun SCXP239778695 BCX0\" lang=\"EN-IN\" xml:lang=\"EN-IN\"><span class=\"NormalTextRun SCXP239778695 BCX0\">Explore our\u00a0<strong><a href=\"https:\/\/www.cloudthat.com\/consulting\/\" target=\"_blank\" rel=\"noopener\">consulting here<\/a>.<\/strong><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/p>\n<p><span style=\"color: #000000;\">If you have any queries regarding<span id=\"aiosp_snippet_title\">\u00a0PostgreSQL and PostgreSQL CTE, Upsert,<\/span>\u00a0or any other service, drop a line below the comments section. I will get back to you at the earliest.<\/span><\/p>\n<h2><strong><span style=\"color: #000080;\">FAQs<\/span><\/strong><\/h2>\n<ol>\n<li>\n<h4><span style=\"text-decoration: underline;\"><span style=\"color: #000000; text-decoration: underline;\"><strong>What is PostgreSQL, and what does it cost?<\/strong><\/span><\/span><\/h4>\n<\/li>\n<\/ol>\n<p><span style=\"color: #000000;\">PostgreSQL is an object-relational database system with the functionality of conventional proprietary database systems. The entire source code for PostgreSQL is available for everyone, and it is free.<\/span><\/p>\n<ol start=\"2\">\n<li><span style=\"text-decoration: underline;\"><strong><span style=\"color: #000000; text-decoration: underline;\">Where can I get PostgreSQL?<\/span><\/strong><\/span><\/li>\n<\/ol>\n<p><span style=\"color: #000000;\">You can obtain the source code from\u00a0<a href=\"https:\/\/www.postgresql.org\/ftp\/\">https:\/\/www.postgresql.org\/ftp\/<\/a><\/span><\/p>\n<ol start=\"3\">\n<li><span style=\"text-decoration: underline;\"><strong><span style=\"color: #000000; text-decoration: underline;\">Can we execute a CTE without a statement in the end?<\/span><\/strong><\/span><\/li>\n<\/ol>\n<p><span style=\"color: #000000;\">No, You have to give a statement in the end to execute a CTE query.<\/span><\/p>\n","protected":false},"author":281,"featured_media":14325,"parent":0,"comment_status":"open","ping_status":"open","template":"","blog_category":[3606,3607,3624],"user_email":"pattan.i@cloudthat.com","published_by":"324","primary-authors":"","secondary-authors":"","acf":[],"_links":{"self":[{"href":"https:\/\/www.cloudthat.com\/resources\/wp-json\/wp\/v2\/blog\/14164"}],"collection":[{"href":"https:\/\/www.cloudthat.com\/resources\/wp-json\/wp\/v2\/blog"}],"about":[{"href":"https:\/\/www.cloudthat.com\/resources\/wp-json\/wp\/v2\/types\/blog"}],"author":[{"embeddable":true,"href":"https:\/\/www.cloudthat.com\/resources\/wp-json\/wp\/v2\/users\/281"}],"replies":[{"embeddable":true,"href":"https:\/\/www.cloudthat.com\/resources\/wp-json\/wp\/v2\/comments?post=14164"}],"version-history":[{"count":1,"href":"https:\/\/www.cloudthat.com\/resources\/wp-json\/wp\/v2\/blog\/14164\/revisions"}],"predecessor-version":[{"id":45667,"href":"https:\/\/www.cloudthat.com\/resources\/wp-json\/wp\/v2\/blog\/14164\/revisions\/45667"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.cloudthat.com\/resources\/wp-json\/"}],"wp:attachment":[{"href":"https:\/\/www.cloudthat.com\/resources\/wp-json\/wp\/v2\/media?parent=14164"}],"wp:term":[{"taxonomy":"blog_category","embeddable":true,"href":"https:\/\/www.cloudthat.com\/resources\/wp-json\/wp\/v2\/blog_category?post=14164"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}