{"id":123403,"date":"2024-06-03T12:18:49","date_gmt":"2024-06-03T09:18:49","guid":{"rendered":"https:\/\/www.javacodegeeks.com\/?p=123403"},"modified":"2024-06-03T12:18:51","modified_gmt":"2024-06-03T09:18:51","slug":"postgresql-recursive-ctes","status":"publish","type":"post","link":"https:\/\/www.javacodegeeks.com\/postgresql-recursive-ctes.html","title":{"rendered":"PostgreSQL \u2013 Recursive CTEs"},"content":{"rendered":"<p><a href=\"https:\/\/www.postgresql.org\/docs\/current\/queries-with.html\" target=\"_blank\" rel=\"noopener\">Common Table Expressions (CTEs)<\/a> are a powerful feature in SQL that allows you to create temporary result sets that can be referenced within a SELECT, INSERT, UPDATE, or DELETE statement. Recursive CTEs extend this capability by allowing a CTE to reference itself, enabling the execution of recursive queries. Let us delve into understanding recursive cte\u2019s.<\/p>\n<h2><a name=\"introduction\"><\/a>1. What is a Recursive CTE?<\/h2>\n<p>A <em>recursive CTE<\/em> is a CTE that references itself, typically used to traverse hierarchical or tree-structured data. It consists of two parts:<\/p>\n<ul>\n<li>The anchor member is a non-recursive query.<\/li>\n<li>The recursive member is a recursive query that references the CTE itself.<\/li>\n<\/ul>\n<p>The recursive query repeatedly applies the recursive member to its output until no more rows are returned.<\/p>\n<h3>1.1 Advantages of Recursive CTEs<\/h3>\n<ul>\n<li>Simplicity and Readability: Recursive CTEs allow complex hierarchical and recursive queries to be written in a simple and readable manner, improving maintainability.<\/li>\n<li>Modularity: CTEs break down complex queries into modular parts, making them easier to understand and debug.<\/li>\n<li>Performance: Recursive CTEs can be more efficient than other methods for processing hierarchical data, such as self-joins or loops in application code.<\/li>\n<li>Flexibility: They provide a flexible way to perform complex recursive operations directly within SQL, without the need for procedural code.<\/li>\n<li>Temporary Storage: CTEs use temporary storage that is automatically managed, freeing developers from the overhead of managing temporary tables.<\/li>\n<\/ul>\n<h3>1.2 Use Cases of Recursive CTEs<\/h3>\n<ul>\n<li>Hierarchical Data: Managing and querying hierarchical data such as organizational structures, file systems, and bills of materials.<\/li>\n<li>Graph Traversal: Performing graph traversal operations, such as finding all paths in a network, identifying connected components, or detecting cycles.<\/li>\n<li>Data Aggregation: Aggregating data at different levels of a hierarchy, such as calculating total sales for a company, departments, and individual employees.<\/li>\n<li>Generating Sequences: Creating sequences of numbers or dates, such as generating a Fibonacci series or producing a list of dates for a given range.<\/li>\n<li>Tree Structures: Working with tree structures, including querying and manipulating trees in decision support systems or game development.<\/li>\n<\/ul>\n<h2><a name=\"working-example\"><\/a>2. Working Example<\/h2>\n<p>Let us dive into some practice implementation on the postgresql database.<div style=\"display:inline-block; margin: 15px 0;\"> <div id=\"adngin-JavaCodeGeeks_incontent_video-0\" style=\"display:inline-block;\"><\/div> <\/div><\/p>\n<h3>2.1 Pre-requirement &#8211; Postgres Setup<\/h3>\n<p>Usually, setting up the database is a tedious step but with <a href=\"https:\/\/www.docker.com\/\" target=\"_blank\" rel=\"noopener\">Docker<\/a>, it is a simple process. You can watch the video available at this <a href=\"https:\/\/www.youtube.com\/watch?v=S7NVloq0EBc\" target=\"_blank\" rel=\"noopener\">link<\/a> to understand the Docker installation on Windows OS. Once done open the terminal and trigger the below command to set and run postgresql.<\/p>\n<p><span style=\"text-decoration: underline\"><em>Docker commands<\/em><\/span><\/p>\n<pre class=\"brush:plain; wrap-lines:false;\">-- command to run Postgres on docker \u2013\n\n-- remember to change the password --\ndocker run -d -p 5433:5432 -e POSTGRES_PASSWORD= --name postgres postgres\n\n-- command to stop the Postgres docker container --\ndocker stop postgres\n\n-- command to remove the Postgres docker container --\ndocker rm postgres\n<\/pre>\n<p>Remember to enter the password of your choice. If everything goes well the postgresql database server will be up and running on a port number \u2013 <code>5433<\/code> and you can connect with the <a href=\"https:\/\/dbeaver.io\/\" target=\"_blank\" rel=\"noopener\">Dbeaver<\/a> GUI tool to connect to the server.<\/p>\n<div class=\"wp-block-image\">\n<figure class=\"aligncenter size-full\"><a href=\"https:\/\/www.javacodegeeks.com\/wp-content\/uploads\/2024\/06\/postgressetupondocker.jpg\"><img decoding=\"async\" width=\"818\" height=\"133\" src=\"https:\/\/www.javacodegeeks.com\/wp-content\/uploads\/2024\/06\/postgressetupondocker.jpg\" alt=\"Recursive CTE's in PostgreSQL-img1\" class=\"wp-image-123404\" srcset=\"https:\/\/www.javacodegeeks.com\/wp-content\/uploads\/2024\/06\/postgressetupondocker.jpg 818w, https:\/\/www.javacodegeeks.com\/wp-content\/uploads\/2024\/06\/postgressetupondocker-300x49.jpg 300w, https:\/\/www.javacodegeeks.com\/wp-content\/uploads\/2024\/06\/postgressetupondocker-768x125.jpg 768w\" sizes=\"(max-width: 818px) 100vw, 818px\" \/><\/a><figcaption class=\"wp-element-caption\">Fig. 1. Postgres on docker<\/figcaption><\/figure>\n<\/div>\n<h3>2.2 Example: Organization Hierarchy<\/h3>\n<p>Consider an organization hierarchy stored in a table <code>employees<\/code>:<\/p>\n<pre class=\"brush:sql; wrap-lines:false;\">\ncreate table employees\n(\n    employee_id SERIAL primary key,\n    employee_name VARCHAR(100),\n    manager_id INTEGER\n        references employees (employee_id)\n);\n\ninsert\n\tinto\n\temployees\n(\n    employee_name,\n\tmanager_id\n)\nvalues\n('Alice',\nnull),\n('Bob',\n1),\n('Charlie',\n1),\n('David',\n2),\n('Eve',\n2),\n('Frank',\n3);\n<\/pre>\n<p>This table defines a simple hierarchy with Alice as the top-level manager. Bob and Charlie report to Alice, David and Eve report to Bob, and Frank reports to Charlie.<\/p>\n<div class=\"wp-block-image\">\n<figure class=\"aligncenter size-full\"><a href=\"https:\/\/www.javacodegeeks.com\/wp-content\/uploads\/2024\/06\/mockdataoutput1.jpg\"><img decoding=\"async\" width=\"428\" height=\"203\" src=\"https:\/\/www.javacodegeeks.com\/wp-content\/uploads\/2024\/06\/mockdataoutput1.jpg\" alt=\"Recursive CTE's in PostgreSQL-img2\" class=\"wp-image-123405\" srcset=\"https:\/\/www.javacodegeeks.com\/wp-content\/uploads\/2024\/06\/mockdataoutput1.jpg 428w, https:\/\/www.javacodegeeks.com\/wp-content\/uploads\/2024\/06\/mockdataoutput1-300x142.jpg 300w\" sizes=\"(max-width: 428px) 100vw, 428px\" \/><\/a><figcaption class=\"wp-element-caption\">Fig. 2: Mocked data<\/figcaption><\/figure>\n<\/div>\n<p>We can use a recursive CTE to list all employees in the hierarchy, starting from the top-level manager:<\/p>\n<pre class=\"brush:sql; wrap-lines:false;\">\nwith recursive employee_hierarchy as (\n-- Anchor member\nselect\n\temployee_id,\n\temployee_name,\n\tmanager_id,\n\t1 as level\nfrom\n\temployees\nwhere\n\tmanager_id is null\nunion all\n-- Recursive member\nselect\n\te.employee_id,\n\te.employee_name,\n\te.manager_id,\n\teh.level + 1 as level\nfrom\n\temployees e\ninner join employee_hierarchy eh on\n\te.manager_id = eh.employee_id\n)\nselect\n\temployee_id,\n\temployee_name,\n\tmanager_id,\n\tlevel\nfrom\n\temployee_hierarchy\norder by\n\tlevel,\n\tmanager_id;\n<\/pre>\n<p>In this query:<\/p>\n<ul>\n<li>The anchor member selects the top-level manager(s) (those without a manager).<\/li>\n<li>The recursive member joins the employees&#8217; table with the CTE to find subordinates of the current level employees, incrementing the level each time.<\/li>\n<\/ul>\n<div class=\"wp-block-image\">\n<figure class=\"aligncenter size-full\"><a href=\"https:\/\/www.javacodegeeks.com\/wp-content\/uploads\/2024\/06\/recursivecteoutput1.jpg\"><img decoding=\"async\" width=\"487\" height=\"201\" src=\"https:\/\/www.javacodegeeks.com\/wp-content\/uploads\/2024\/06\/recursivecteoutput1.jpg\" alt=\"Recursive CTE's in PostgreSQL-img3\" class=\"wp-image-123406\" srcset=\"https:\/\/www.javacodegeeks.com\/wp-content\/uploads\/2024\/06\/recursivecteoutput1.jpg 487w, https:\/\/www.javacodegeeks.com\/wp-content\/uploads\/2024\/06\/recursivecteoutput1-300x124.jpg 300w\" sizes=\"(max-width: 487px) 100vw, 487px\" \/><\/a><figcaption class=\"wp-element-caption\">Fig. 3: Recursive cte output 1<\/figcaption><\/figure>\n<\/div>\n<h3>2.3 Example: Fibonacci Sequence<\/h3>\n<p>Recursive CTEs can also be used for generating sequences. For example, the Fibonacci sequence:<\/p>\n<pre class=\"brush:sql; wrap-lines:false;\">\nwith recursive fibonacci as (\nselect\n\t1 as n,\n\t0 as fib1,\n\t1 as fib2\nunion all\nselect\n\tn + 1,\n\tfib2,\n\tfib1 + fib2\nfrom\n\tfibonacci\nwhere\n\tn &lt; 10\n)\nselect\n\tn,\n\tfib1 as fibonacci_number\nfrom\n\tfibonacci;\n<\/pre>\n<p>In this query:<\/p>\n<ul>\n<li>The anchor member starts the sequence with the first Fibonacci number (0).<\/li>\n<li>The recursive member generates the next Fibonacci number by summing the last two numbers in the sequence.<\/li>\n<\/ul>\n<div class=\"wp-block-image\">\n<figure class=\"aligncenter size-full\"><a href=\"https:\/\/www.javacodegeeks.com\/wp-content\/uploads\/2024\/06\/recursivecteoutput2.jpg\"><img decoding=\"async\" width=\"325\" height=\"242\" src=\"https:\/\/www.javacodegeeks.com\/wp-content\/uploads\/2024\/06\/recursivecteoutput2.jpg\" alt=\"Recursive CTE's in PostgreSQL-img4\" class=\"wp-image-123407\" srcset=\"https:\/\/www.javacodegeeks.com\/wp-content\/uploads\/2024\/06\/recursivecteoutput2.jpg 325w, https:\/\/www.javacodegeeks.com\/wp-content\/uploads\/2024\/06\/recursivecteoutput2-300x223.jpg 300w\" sizes=\"(max-width: 325px) 100vw, 325px\" \/><\/a><figcaption class=\"wp-element-caption\">Fig. 4: Recursive cte output 2<\/figcaption><\/figure>\n<\/div>\n<h2><a name=\"conclusion\"><\/a>3. Conclusion<\/h2>\n<p>Recursive CTEs in PostgreSQL are a powerful tool for working with hierarchical and recursive data structures. By understanding and leveraging recursive CTEs, you can perform complex queries that would otherwise require more intricate and less efficient solutions.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Common Table Expressions (CTEs) are a powerful feature in SQL that allows you to create temporary result sets that can be referenced within a SELECT, INSERT, UPDATE, or DELETE statement. Recursive CTEs extend this capability by allowing a CTE to reference itself, enabling the execution of recursive queries. Let us delve into understanding recursive cte\u2019s. &hellip;<\/p>\n","protected":false},"author":26931,"featured_media":2456,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[8],"tags":[2719,657,244],"class_list":["post-123403","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-enterprise-java","tag-cte","tag-postgresql","tag-sql"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v27.5 - https:\/\/yoast.com\/product\/yoast-seo-wordpress\/ -->\n<title>PostgreSQL \u2013 Recursive CTEs - Java Code Geeks<\/title>\n<meta name=\"description\" content=\"Recursive CTE&#039;s in PostgreSQL: Learn about Recursive CTEs in PostgreSQL, their advantages, use cases, and examples.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.javacodegeeks.com\/postgresql-recursive-ctes.html\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"PostgreSQL \u2013 Recursive CTEs - Java Code Geeks\" \/>\n<meta property=\"og:description\" content=\"Recursive CTE&#039;s in PostgreSQL: Learn about Recursive CTEs in PostgreSQL, their advantages, use cases, and examples.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.javacodegeeks.com\/postgresql-recursive-ctes.html\" \/>\n<meta property=\"og:site_name\" content=\"Java Code Geeks\" \/>\n<meta property=\"article:publisher\" content=\"https:\/\/www.facebook.com\/javacodegeeks\" \/>\n<meta property=\"article:published_time\" content=\"2024-06-03T09:18:49+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2024-06-03T09:18:51+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/www.javacodegeeks.com\/wp-content\/uploads\/2012\/10\/postgresql-logo.jpg\" \/>\n\t<meta property=\"og:image:width\" content=\"150\" \/>\n\t<meta property=\"og:image:height\" content=\"150\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/jpeg\" \/>\n<meta name=\"author\" content=\"Yatin Batra\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@javacodegeeks\" \/>\n<meta name=\"twitter:site\" content=\"@javacodegeeks\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Yatin Batra\" \/>\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:\\\/\\\/www.javacodegeeks.com\\\/postgresql-recursive-ctes.html#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.javacodegeeks.com\\\/postgresql-recursive-ctes.html\"},\"author\":{\"name\":\"Yatin Batra\",\"@id\":\"https:\\\/\\\/www.javacodegeeks.com\\\/#\\\/schema\\\/person\\\/cda31a4c1965373fed40c8907dc09b8d\"},\"headline\":\"PostgreSQL \u2013 Recursive CTEs\",\"datePublished\":\"2024-06-03T09:18:49+00:00\",\"dateModified\":\"2024-06-03T09:18:51+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/www.javacodegeeks.com\\\/postgresql-recursive-ctes.html\"},\"wordCount\":635,\"commentCount\":0,\"publisher\":{\"@id\":\"https:\\\/\\\/www.javacodegeeks.com\\\/#organization\"},\"image\":{\"@id\":\"https:\\\/\\\/www.javacodegeeks.com\\\/postgresql-recursive-ctes.html#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/www.javacodegeeks.com\\\/wp-content\\\/uploads\\\/2012\\\/10\\\/postgresql-logo.jpg\",\"keywords\":[\"cte\",\"PostgreSQL\",\"SQL\"],\"articleSection\":[\"Enterprise Java\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/www.javacodegeeks.com\\\/postgresql-recursive-ctes.html#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/www.javacodegeeks.com\\\/postgresql-recursive-ctes.html\",\"url\":\"https:\\\/\\\/www.javacodegeeks.com\\\/postgresql-recursive-ctes.html\",\"name\":\"PostgreSQL \u2013 Recursive CTEs - Java Code Geeks\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.javacodegeeks.com\\\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\\\/\\\/www.javacodegeeks.com\\\/postgresql-recursive-ctes.html#primaryimage\"},\"image\":{\"@id\":\"https:\\\/\\\/www.javacodegeeks.com\\\/postgresql-recursive-ctes.html#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/www.javacodegeeks.com\\\/wp-content\\\/uploads\\\/2012\\\/10\\\/postgresql-logo.jpg\",\"datePublished\":\"2024-06-03T09:18:49+00:00\",\"dateModified\":\"2024-06-03T09:18:51+00:00\",\"description\":\"Recursive CTE's in PostgreSQL: Learn about Recursive CTEs in PostgreSQL, their advantages, use cases, and examples.\",\"breadcrumb\":{\"@id\":\"https:\\\/\\\/www.javacodegeeks.com\\\/postgresql-recursive-ctes.html#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/www.javacodegeeks.com\\\/postgresql-recursive-ctes.html\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/www.javacodegeeks.com\\\/postgresql-recursive-ctes.html#primaryimage\",\"url\":\"https:\\\/\\\/www.javacodegeeks.com\\\/wp-content\\\/uploads\\\/2012\\\/10\\\/postgresql-logo.jpg\",\"contentUrl\":\"https:\\\/\\\/www.javacodegeeks.com\\\/wp-content\\\/uploads\\\/2012\\\/10\\\/postgresql-logo.jpg\",\"width\":150,\"height\":150},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/www.javacodegeeks.com\\\/postgresql-recursive-ctes.html#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/www.javacodegeeks.com\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Java\",\"item\":\"https:\\\/\\\/www.javacodegeeks.com\\\/category\\\/java\"},{\"@type\":\"ListItem\",\"position\":3,\"name\":\"Enterprise Java\",\"item\":\"https:\\\/\\\/www.javacodegeeks.com\\\/category\\\/java\\\/enterprise-java\"},{\"@type\":\"ListItem\",\"position\":4,\"name\":\"PostgreSQL \u2013 Recursive CTEs\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/www.javacodegeeks.com\\\/#website\",\"url\":\"https:\\\/\\\/www.javacodegeeks.com\\\/\",\"name\":\"Java Code Geeks\",\"description\":\"Java Developers Resource Center\",\"publisher\":{\"@id\":\"https:\\\/\\\/www.javacodegeeks.com\\\/#organization\"},\"alternateName\":\"JCG\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/www.javacodegeeks.com\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Organization\",\"@id\":\"https:\\\/\\\/www.javacodegeeks.com\\\/#organization\",\"name\":\"Exelixis Media P.C.\",\"url\":\"https:\\\/\\\/www.javacodegeeks.com\\\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/www.javacodegeeks.com\\\/#\\\/schema\\\/logo\\\/image\\\/\",\"url\":\"https:\\\/\\\/www.javacodegeeks.com\\\/wp-content\\\/uploads\\\/2022\\\/06\\\/exelixis-logo.png\",\"contentUrl\":\"https:\\\/\\\/www.javacodegeeks.com\\\/wp-content\\\/uploads\\\/2022\\\/06\\\/exelixis-logo.png\",\"width\":864,\"height\":246,\"caption\":\"Exelixis Media P.C.\"},\"image\":{\"@id\":\"https:\\\/\\\/www.javacodegeeks.com\\\/#\\\/schema\\\/logo\\\/image\\\/\"},\"sameAs\":[\"https:\\\/\\\/www.facebook.com\\\/javacodegeeks\",\"https:\\\/\\\/x.com\\\/javacodegeeks\"]},{\"@type\":\"Person\",\"@id\":\"https:\\\/\\\/www.javacodegeeks.com\\\/#\\\/schema\\\/person\\\/cda31a4c1965373fed40c8907dc09b8d\",\"name\":\"Yatin Batra\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/www.javacodegeeks.com\\\/wp-content\\\/uploads\\\/2022\\\/12\\\/Yatin.batra_.jpg\",\"url\":\"https:\\\/\\\/www.javacodegeeks.com\\\/wp-content\\\/uploads\\\/2022\\\/12\\\/Yatin.batra_.jpg\",\"contentUrl\":\"https:\\\/\\\/www.javacodegeeks.com\\\/wp-content\\\/uploads\\\/2022\\\/12\\\/Yatin.batra_.jpg\",\"caption\":\"Yatin Batra\"},\"description\":\"An experience full-stack engineer well versed with Core Java, Spring\\\/Springboot, MVC, Security, AOP, Frontend (Angular &amp; React), and cloud technologies (such as AWS, GCP, Jenkins, Docker, K8).\",\"sameAs\":[\"https:\\\/\\\/www.javacodegeeks.com\"],\"url\":\"https:\\\/\\\/www.javacodegeeks.com\\\/author\\\/yatin-batra\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"PostgreSQL \u2013 Recursive CTEs - Java Code Geeks","description":"Recursive CTE's in PostgreSQL: Learn about Recursive CTEs in PostgreSQL, their advantages, use cases, and examples.","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:\/\/www.javacodegeeks.com\/postgresql-recursive-ctes.html","og_locale":"en_US","og_type":"article","og_title":"PostgreSQL \u2013 Recursive CTEs - Java Code Geeks","og_description":"Recursive CTE's in PostgreSQL: Learn about Recursive CTEs in PostgreSQL, their advantages, use cases, and examples.","og_url":"https:\/\/www.javacodegeeks.com\/postgresql-recursive-ctes.html","og_site_name":"Java Code Geeks","article_publisher":"https:\/\/www.facebook.com\/javacodegeeks","article_published_time":"2024-06-03T09:18:49+00:00","article_modified_time":"2024-06-03T09:18:51+00:00","og_image":[{"width":150,"height":150,"url":"https:\/\/www.javacodegeeks.com\/wp-content\/uploads\/2012\/10\/postgresql-logo.jpg","type":"image\/jpeg"}],"author":"Yatin Batra","twitter_card":"summary_large_image","twitter_creator":"@javacodegeeks","twitter_site":"@javacodegeeks","twitter_misc":{"Written by":"Yatin Batra","Est. reading time":"4 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.javacodegeeks.com\/postgresql-recursive-ctes.html#article","isPartOf":{"@id":"https:\/\/www.javacodegeeks.com\/postgresql-recursive-ctes.html"},"author":{"name":"Yatin Batra","@id":"https:\/\/www.javacodegeeks.com\/#\/schema\/person\/cda31a4c1965373fed40c8907dc09b8d"},"headline":"PostgreSQL \u2013 Recursive CTEs","datePublished":"2024-06-03T09:18:49+00:00","dateModified":"2024-06-03T09:18:51+00:00","mainEntityOfPage":{"@id":"https:\/\/www.javacodegeeks.com\/postgresql-recursive-ctes.html"},"wordCount":635,"commentCount":0,"publisher":{"@id":"https:\/\/www.javacodegeeks.com\/#organization"},"image":{"@id":"https:\/\/www.javacodegeeks.com\/postgresql-recursive-ctes.html#primaryimage"},"thumbnailUrl":"https:\/\/www.javacodegeeks.com\/wp-content\/uploads\/2012\/10\/postgresql-logo.jpg","keywords":["cte","PostgreSQL","SQL"],"articleSection":["Enterprise Java"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.javacodegeeks.com\/postgresql-recursive-ctes.html#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.javacodegeeks.com\/postgresql-recursive-ctes.html","url":"https:\/\/www.javacodegeeks.com\/postgresql-recursive-ctes.html","name":"PostgreSQL \u2013 Recursive CTEs - Java Code Geeks","isPartOf":{"@id":"https:\/\/www.javacodegeeks.com\/#website"},"primaryImageOfPage":{"@id":"https:\/\/www.javacodegeeks.com\/postgresql-recursive-ctes.html#primaryimage"},"image":{"@id":"https:\/\/www.javacodegeeks.com\/postgresql-recursive-ctes.html#primaryimage"},"thumbnailUrl":"https:\/\/www.javacodegeeks.com\/wp-content\/uploads\/2012\/10\/postgresql-logo.jpg","datePublished":"2024-06-03T09:18:49+00:00","dateModified":"2024-06-03T09:18:51+00:00","description":"Recursive CTE's in PostgreSQL: Learn about Recursive CTEs in PostgreSQL, their advantages, use cases, and examples.","breadcrumb":{"@id":"https:\/\/www.javacodegeeks.com\/postgresql-recursive-ctes.html#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.javacodegeeks.com\/postgresql-recursive-ctes.html"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.javacodegeeks.com\/postgresql-recursive-ctes.html#primaryimage","url":"https:\/\/www.javacodegeeks.com\/wp-content\/uploads\/2012\/10\/postgresql-logo.jpg","contentUrl":"https:\/\/www.javacodegeeks.com\/wp-content\/uploads\/2012\/10\/postgresql-logo.jpg","width":150,"height":150},{"@type":"BreadcrumbList","@id":"https:\/\/www.javacodegeeks.com\/postgresql-recursive-ctes.html#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.javacodegeeks.com\/"},{"@type":"ListItem","position":2,"name":"Java","item":"https:\/\/www.javacodegeeks.com\/category\/java"},{"@type":"ListItem","position":3,"name":"Enterprise Java","item":"https:\/\/www.javacodegeeks.com\/category\/java\/enterprise-java"},{"@type":"ListItem","position":4,"name":"PostgreSQL \u2013 Recursive CTEs"}]},{"@type":"WebSite","@id":"https:\/\/www.javacodegeeks.com\/#website","url":"https:\/\/www.javacodegeeks.com\/","name":"Java Code Geeks","description":"Java Developers Resource Center","publisher":{"@id":"https:\/\/www.javacodegeeks.com\/#organization"},"alternateName":"JCG","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.javacodegeeks.com\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/www.javacodegeeks.com\/#organization","name":"Exelixis Media P.C.","url":"https:\/\/www.javacodegeeks.com\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.javacodegeeks.com\/#\/schema\/logo\/image\/","url":"https:\/\/www.javacodegeeks.com\/wp-content\/uploads\/2022\/06\/exelixis-logo.png","contentUrl":"https:\/\/www.javacodegeeks.com\/wp-content\/uploads\/2022\/06\/exelixis-logo.png","width":864,"height":246,"caption":"Exelixis Media P.C."},"image":{"@id":"https:\/\/www.javacodegeeks.com\/#\/schema\/logo\/image\/"},"sameAs":["https:\/\/www.facebook.com\/javacodegeeks","https:\/\/x.com\/javacodegeeks"]},{"@type":"Person","@id":"https:\/\/www.javacodegeeks.com\/#\/schema\/person\/cda31a4c1965373fed40c8907dc09b8d","name":"Yatin Batra","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.javacodegeeks.com\/wp-content\/uploads\/2022\/12\/Yatin.batra_.jpg","url":"https:\/\/www.javacodegeeks.com\/wp-content\/uploads\/2022\/12\/Yatin.batra_.jpg","contentUrl":"https:\/\/www.javacodegeeks.com\/wp-content\/uploads\/2022\/12\/Yatin.batra_.jpg","caption":"Yatin Batra"},"description":"An experience full-stack engineer well versed with Core Java, Spring\/Springboot, MVC, Security, AOP, Frontend (Angular &amp; React), and cloud technologies (such as AWS, GCP, Jenkins, Docker, K8).","sameAs":["https:\/\/www.javacodegeeks.com"],"url":"https:\/\/www.javacodegeeks.com\/author\/yatin-batra"}]}},"_links":{"self":[{"href":"https:\/\/www.javacodegeeks.com\/wp-json\/wp\/v2\/posts\/123403","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.javacodegeeks.com\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.javacodegeeks.com\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.javacodegeeks.com\/wp-json\/wp\/v2\/users\/26931"}],"replies":[{"embeddable":true,"href":"https:\/\/www.javacodegeeks.com\/wp-json\/wp\/v2\/comments?post=123403"}],"version-history":[{"count":0,"href":"https:\/\/www.javacodegeeks.com\/wp-json\/wp\/v2\/posts\/123403\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.javacodegeeks.com\/wp-json\/wp\/v2\/media\/2456"}],"wp:attachment":[{"href":"https:\/\/www.javacodegeeks.com\/wp-json\/wp\/v2\/media?parent=123403"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.javacodegeeks.com\/wp-json\/wp\/v2\/categories?post=123403"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.javacodegeeks.com\/wp-json\/wp\/v2\/tags?post=123403"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}