{"id":1838,"date":"2016-09-28T17:15:17","date_gmt":"2016-09-28T14:15:17","guid":{"rendered":"http:\/\/www.systemcodegeeks.com\/?p=1838"},"modified":"2017-12-04T16:06:39","modified_gmt":"2017-12-04T14:06:39","slug":"common-table-expressions-postgresql","status":"publish","type":"post","link":"https:\/\/www.systemcodegeeks.com\/databases\/postgresql\/common-table-expressions-postgresql\/","title":{"rendered":"Common Table Expressions in PostgreSQL"},"content":{"rendered":"

This article is part of our Academy Course titled PostgreSQL Database Tutorial<\/a>.<\/p>\n

In this course, we provide a compilation of PostgreSQL tutorials that will help you set up and run your own database management system. We cover a wide range of topics, from installation and configuration, to custom commands and datatypes. With our straightforward tutorials, you will be able to get your own projects up and running in minimum time. Check it out here<\/a>!<\/em><\/p>\n

In our previous article<\/a> we explained how to use JOINs<\/strong> to create more advanced SELECT<\/strong> queries. However, there are instances when using this technique to retrieve data from two or more tables does not satisfy our requirements or makes the query difficult to read – for example, if we need several JOINs or a subquery to return the desired information.<\/p>\n

To solve this, standard SQL (note that this is not something exclusive to PostgreSQL), introduced the concept of Common Table Expressions<\/em> (best known as CTE for short) in order to simplify this type of queries. In this article we will explain what CTEs are and how to use them.
\n[ulp id=’mezv5fambxsNcQw8′]<\/p>\n

1.Definition of Common Table Expressions (CTE)<\/h2>\n

Formally speaking, a CTE is a temporary result set that is created through the use of a WITH clause and is valid only during the execution of a given query. Another distinguishing feature of a CTE is that it can either reference itself (recursive CTE) or not (non-recursive CTE), providing the flexibility that common queries do not provide. A recursive CTE is often used when a calculation needs to be reported as part of the final result set, whereas a non-recursive one is usually utilized for a regular query. Additionally, its definition -meaning the fields it returns- is not stored as a separate database object.<\/p>\n

Although Common Table Expressions can be used in SELECT, INSERT, UPDATE, or DELETE operations, we will only use the first type as it is the easiest to understand. Once you feel comfortable with using CTEs that involve SELECTs only, refer to the official PostgreSQL 9.5 documentation<\/a> to learn how to use them with the other operation types.<\/div>\n

All of these new concepts will better sink in as we illustrate them through examples, so let\u2019s begin.<\/p>\n

2.Non-recursive Common Table Expressions<\/h2>\n

As usual, we will use the World_db database we installed in the first article of this series<\/a>. To begin, let\u2019s consider the following query:<\/p>\n

SELECT A.name \"City\", A.district \"District\",\r\nB.name \"Country\", C.language \"Language\"\r\nFROM city A JOIN country B ON A.countrycode=B.code\r\nJOIN countrylanguage C ON A.countrycode=C.countrycode\r\nWHERE A.name='Rosario' AND C.isofficial='TRUE';<\/pre>\n
As you can probably guess by now, it will return the city name, the district, the country, and the official language where the city name is Rosario. If you look carefully, this query uses 2 JOINs<\/strong> – not a bad thing in itself, but the readability certainly could use some improvements.<\/p>\n
Our first example of a Common Table Expression will be rather basic but does the job of introducing the concept:<\/p>\n
WITH t AS (\r\nSELECT A.name City, A.district District,\r\nA.countrycode CountryCode, B.name Country\r\nFROM city A JOIN country B ON A.countrycode=B.code)\r\nSELECT t.City, t.District, t.Country, C.language\r\nFROM t JOIN countrylanguage C on t.CountryCode = C.countrycode\r\nWHERE t.City='Rosario' AND C.isofficial='TRUE';<\/pre>\n
Before we go into PostgreSQL and run the above query, let\u2019s split it into two parts to explain what is happening.<\/p>\n
Step 1 –<\/b> Define the CTE using the WITH clause<\/em>. For simplicity, we will name the CTE as t, but you can use other name if you want.<\/p>\n
WITH t AS (\r\nSELECT A.name City, A.district District,\r\nA.countrycode CountryCode, B.name Country\r\nFROM city A JOIN country B ON A.countrycode=B.code)<\/pre>\n
If we were to do a SELECT * FROM t;<\/code> at this point, we would get all the cities with their corresponding district and country. You may well be saying to yourself, \u201cThen I don\u2019t see what\u2019s the point in using CTEs\u201d – but wait, Step 2 will shed some light on the why.<\/p>\n
Step 2 –<\/b> Select the fields from the CTE and perform a JOIN with another table<\/em>. As the CTE can be considered a temporary result set, we can perform JOINs on other tables. However, in this case we can use the more descriptive names given by the CTE instead of the original table names (are you seeing the readability improvements already?). Since both the city and country tables contain a field called name<\/i><\/b>, the CTE allows us to refer to the city and country names as City and Country instead.<\/p>\n
SELECT t.City, t.District, t.Country, C.language\r\nFROM t JOIN countrylanguage C on t.CountryCode = C.countrycode\r\nWHERE t.City='Rosario' AND C.isofficial='TRUE';<\/pre>\n
As you can see in Fig. 1, the result is identical to the original query:<\/p>\n
\"A<\/a>
Figure 1 – A non-recursive Common Table Expression<\/figcaption><\/figure>\n
Recursive Common Table Expressions<\/b><\/p>\n
A recursive CTE references itself usually via a WITH clause referring to its own output. To better illustrate through an example, we are going to create a new database and table named College and CollegeClasses, respectively, and populate the former with dummy data as follows:<\/p>\n
CREATE TABLE CollegeClasses (\r\nClassID serial PRIMARY KEY,\r\nClassDescription VARCHAR NOT NULL,\r\nClassParentID INT\r\n);\r\n\r\nINSERT INTO CollegeClasses (\r\nClassDescription,\r\nClassParentID\r\n)\r\nVALUES\r\n('Calculus 1', NULL),\r\n('Algebra 1', 1),\r\n('Analytic Geometry', 1),\r\n('Physics 1', 1),\r\n('Statistics', 1),\r\n('Algebra 2', 2),\r\n('Discrete Math', 2),\r\n('Programming 1', 2),\r\n('Programming 2', 2),\r\n('Advanced Geometry', 3),\r\n('Control systems', 3),\r\n('English as a Second Language 1', 3),\r\n('Literature', 3),\r\n('Physics 2', 4),\r\n('Calculus 2', 4),\r\n('Graphs and Math', 7),\r\n('English as a Second Language 2', 7),\r\n('Basic algorithms', 8),\r\n('Advanced algorithms', 8),\r\n('Programming with C', 8);<\/pre>\n
In this case we\u2019re interested in retrieving a list of classes and their children down to a given level. For example, we will start with Algebra 1 (ClassID=2) and descend down to the last class that depends on it:<\/p>\n
WITH RECURSIVE classes AS (\r\nSELECT\r\nClassID,\r\nClassParentID,\r\nClassDescription\r\nFROM\r\nCollegeClasses\r\nWHERE\r\nClassID = 2\r\nUNION\r\nSELECT\r\ne.ClassID,\r\ne.ClassParentID,\r\ne.ClassDescription\r\nFROM\r\nCollegeClasses e\r\nINNER JOIN classes s ON s.ClassID = e.ClassParentID\r\n) SELECT * FROM classes;<\/pre>\n
This query, as in the previous section, deserves a detailed explanation. Let\u2019s begin by saying a recursive CTE consists of 4 components:<\/p>\n
#1 –<\/b> A non-recursive query<\/em>. In this case, it is a query to retrieve the CollegeClass information where ClassID=2:<\/p>\n
SELECT ClassID, ClassParentID, ClassDescription\r\nFROM CollegeClasses WHERE ClassID = 2<\/pre>\n
#2 –<\/b> The UNION or UNION ALL operator<\/em>. Any of these operators allows us to combine one or more result sets into a single one. The choice of one above the other will depend on whether you want to avoid duplicates (if any) or return them, respectively.<\/p>\n
#3 –<\/b> The recursive term<\/em>. Note that the classes temporary table references itself in this part of the CTE:<\/p>\n
SELECT e.ClassID, e.ClassParentID, e.ClassDescription\r\nFROM CollegeClasses e INNER JOIN classes s ON s.ClassID = e.ClassParentID\r\n<\/pre>\n
#4 –<\/b> The final statement, which is executed once the iterations in Part 3 have finished. In this case,<\/p>\n
SELECT * FROM classes;\r\n<\/pre>\n
That said, let\u2019s take a look at the result of the query (see Fig. 2) and examine it to see if it meets our expectations:<\/p>\n
\"A<\/a>
Figure 2 – A recursive CTE<\/figcaption><\/figure>\n
As we can see, the result set begins with ClassID=2, and shows all its children (ClassID=6, 7, 8, and 9). Then it shows all the children of this last set as well.<\/p>\n
You will want to use a recursive CTE if you need to retrieve information in the form of a hierarchical tree. It is precisely the keyword RECURSIVE at the top of the query which allows the CTE to reference itself.<\/div>\n
3.Summary<\/h2>\n
In this article we have explained how to create recursive and non-recursive Common Table Expressions in PostgreSQL. As you pursue the study of this topic, keep in mind that using CTEs is not a matter of improving performance, but readability and maintainability.<\/p>\n
Hope it helps!<\/p>\n","protected":false},"excerpt":{"rendered":"
This article is part of our Academy Course titled PostgreSQL Database Tutorial. In this course, we provide a compilation of PostgreSQL tutorials that will help you set up and run your own database management system. We cover a wide range of topics, from installation and configuration, to custom commands and datatypes. With our straightforward tutorials, …<\/p>\n","protected":false},"author":15,"featured_media":198,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[34],"tags":[],"class_list":["post-1838","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-postgresql"],"yoast_head":"\nCommon Table Expressions in PostgreSQL - System Code Geeks - 2026<\/title>\n<meta name=\"description\" content=\"This article is part of our Academy Course titled PostgreSQL Database Tutorial. In this course, we provide a compilation of PostgreSQL tutorials that will\" \/>\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.systemcodegeeks.com\/databases\/postgresql\/common-table-expressions-postgresql\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Common Table Expressions in PostgreSQL - System Code Geeks - 2026\" \/>\n<meta property=\"og:description\" content=\"This article is part of our Academy Course titled PostgreSQL Database Tutorial. In this course, we provide a compilation of PostgreSQL tutorials that will\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.systemcodegeeks.com\/databases\/postgresql\/common-table-expressions-postgresql\/\" \/>\n<meta property=\"og:site_name\" content=\"System Code Geeks\" \/>\n<meta property=\"article:publisher\" content=\"https:\/\/www.facebook.com\/systemcodegeeks\" \/>\n<meta property=\"article:author\" content=\"https:\/\/www.facebook.com\/gacanepa\" \/>\n<meta property=\"article:published_time\" content=\"2016-09-28T14:15:17+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2017-12-04T14:06:39+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/www.systemcodegeeks.com\/wp-content\/uploads\/2016\/01\/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=\"Gabriel Canepa\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@gacanepa\" \/>\n<meta name=\"twitter:site\" content=\"@systemcodegeeks\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Gabriel Canepa\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"7 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/www.systemcodegeeks.com\/databases\/postgresql\/common-table-expressions-postgresql\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.systemcodegeeks.com\/databases\/postgresql\/common-table-expressions-postgresql\/\"},\"author\":{\"name\":\"Gabriel Canepa\",\"@id\":\"https:\/\/www.systemcodegeeks.com\/#\/schema\/person\/967da353d0f1a1de21c9504942625a5f\"},\"headline\":\"Common Table Expressions in PostgreSQL\",\"datePublished\":\"2016-09-28T14:15:17+00:00\",\"dateModified\":\"2017-12-04T14:06:39+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.systemcodegeeks.com\/databases\/postgresql\/common-table-expressions-postgresql\/\"},\"wordCount\":1056,\"commentCount\":0,\"publisher\":{\"@id\":\"https:\/\/www.systemcodegeeks.com\/#organization\"},\"image\":{\"@id\":\"https:\/\/www.systemcodegeeks.com\/databases\/postgresql\/common-table-expressions-postgresql\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/www.systemcodegeeks.com\/wp-content\/uploads\/2016\/01\/postgresql-logo.jpg\",\"articleSection\":[\"PostgreSQL\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/www.systemcodegeeks.com\/databases\/postgresql\/common-table-expressions-postgresql\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.systemcodegeeks.com\/databases\/postgresql\/common-table-expressions-postgresql\/\",\"url\":\"https:\/\/www.systemcodegeeks.com\/databases\/postgresql\/common-table-expressions-postgresql\/\",\"name\":\"Common Table Expressions in PostgreSQL - System Code Geeks - 2026\",\"isPartOf\":{\"@id\":\"https:\/\/www.systemcodegeeks.com\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/www.systemcodegeeks.com\/databases\/postgresql\/common-table-expressions-postgresql\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/www.systemcodegeeks.com\/databases\/postgresql\/common-table-expressions-postgresql\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/www.systemcodegeeks.com\/wp-content\/uploads\/2016\/01\/postgresql-logo.jpg\",\"datePublished\":\"2016-09-28T14:15:17+00:00\",\"dateModified\":\"2017-12-04T14:06:39+00:00\",\"description\":\"This article is part of our Academy Course titled PostgreSQL Database Tutorial. In this course, we provide a compilation of PostgreSQL tutorials that will\",\"breadcrumb\":{\"@id\":\"https:\/\/www.systemcodegeeks.com\/databases\/postgresql\/common-table-expressions-postgresql\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.systemcodegeeks.com\/databases\/postgresql\/common-table-expressions-postgresql\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.systemcodegeeks.com\/databases\/postgresql\/common-table-expressions-postgresql\/#primaryimage\",\"url\":\"https:\/\/www.systemcodegeeks.com\/wp-content\/uploads\/2016\/01\/postgresql-logo.jpg\",\"contentUrl\":\"https:\/\/www.systemcodegeeks.com\/wp-content\/uploads\/2016\/01\/postgresql-logo.jpg\",\"width\":150,\"height\":150},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.systemcodegeeks.com\/databases\/postgresql\/common-table-expressions-postgresql\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.systemcodegeeks.com\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Databases\",\"item\":\"https:\/\/www.systemcodegeeks.com\/category\/databases\/\"},{\"@type\":\"ListItem\",\"position\":3,\"name\":\"PostgreSQL\",\"item\":\"https:\/\/www.systemcodegeeks.com\/category\/databases\/postgresql\/\"},{\"@type\":\"ListItem\",\"position\":4,\"name\":\"Common Table Expressions in PostgreSQL\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.systemcodegeeks.com\/#website\",\"url\":\"https:\/\/www.systemcodegeeks.com\/\",\"name\":\"System Code Geeks\",\"description\":\"Operating System Developers Resource Center\",\"publisher\":{\"@id\":\"https:\/\/www.systemcodegeeks.com\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.systemcodegeeks.com\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Organization\",\"@id\":\"https:\/\/www.systemcodegeeks.com\/#organization\",\"name\":\"Exelixis Media P.C.\",\"url\":\"https:\/\/www.systemcodegeeks.com\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.systemcodegeeks.com\/#\/schema\/logo\/image\/\",\"url\":\"https:\/\/www.systemcodegeeks.com\/wp-content\/uploads\/2022\/06\/exelixis-logo.png\",\"contentUrl\":\"https:\/\/www.systemcodegeeks.com\/wp-content\/uploads\/2022\/06\/exelixis-logo.png\",\"width\":864,\"height\":246,\"caption\":\"Exelixis Media P.C.\"},\"image\":{\"@id\":\"https:\/\/www.systemcodegeeks.com\/#\/schema\/logo\/image\/\"},\"sameAs\":[\"https:\/\/www.facebook.com\/systemcodegeeks\",\"https:\/\/x.com\/systemcodegeeks\"]},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.systemcodegeeks.com\/#\/schema\/person\/967da353d0f1a1de21c9504942625a5f\",\"name\":\"Gabriel Canepa\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.systemcodegeeks.com\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/27b3ea2a3fb1de4ed1c8694a1465c099a86586d8b833a0d852a26d76d750df9f?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/27b3ea2a3fb1de4ed1c8694a1465c099a86586d8b833a0d852a26d76d750df9f?s=96&d=mm&r=g\",\"caption\":\"Gabriel Canepa\"},\"description\":\"Gabriel Canepa is a Linux Foundation Certified System Administrator (LFCS-1500-0576-0100) and web developer from Villa Mercedes, San Luis, Argentina. He works for a worldwide leading consumer product company and takes great pleasure in using FOSS tools to increase productivity in all areas of his daily work. When he's not typing commands or writing code or articles, he enjoys telling bedtime stories with his wife to his two little daughters and playing with them, the great pleasure of his life.\",\"sameAs\":[\"http:\/\/www.gabrielcanepa.com.ar\/\",\"https:\/\/www.facebook.com\/gacanepa\",\"https:\/\/ar.linkedin.com\/in\/gacanepa\",\"https:\/\/x.com\/gacanepa\"],\"url\":\"https:\/\/www.systemcodegeeks.com\/author\/gabriel-canepa\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Common Table Expressions in PostgreSQL - System Code Geeks - 2026","description":"This article is part of our Academy Course titled PostgreSQL Database Tutorial. In this course, we provide a compilation of PostgreSQL tutorials that will","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.systemcodegeeks.com\/databases\/postgresql\/common-table-expressions-postgresql\/","og_locale":"en_US","og_type":"article","og_title":"Common Table Expressions in PostgreSQL - System Code Geeks - 2026","og_description":"This article is part of our Academy Course titled PostgreSQL Database Tutorial. In this course, we provide a compilation of PostgreSQL tutorials that will","og_url":"https:\/\/www.systemcodegeeks.com\/databases\/postgresql\/common-table-expressions-postgresql\/","og_site_name":"System Code Geeks","article_publisher":"https:\/\/www.facebook.com\/systemcodegeeks","article_author":"https:\/\/www.facebook.com\/gacanepa","article_published_time":"2016-09-28T14:15:17+00:00","article_modified_time":"2017-12-04T14:06:39+00:00","og_image":[{"width":150,"height":150,"url":"https:\/\/www.systemcodegeeks.com\/wp-content\/uploads\/2016\/01\/postgresql-logo.jpg","type":"image\/jpeg"}],"author":"Gabriel Canepa","twitter_card":"summary_large_image","twitter_creator":"@gacanepa","twitter_site":"@systemcodegeeks","twitter_misc":{"Written by":"Gabriel Canepa","Est. reading time":"7 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.systemcodegeeks.com\/databases\/postgresql\/common-table-expressions-postgresql\/#article","isPartOf":{"@id":"https:\/\/www.systemcodegeeks.com\/databases\/postgresql\/common-table-expressions-postgresql\/"},"author":{"name":"Gabriel Canepa","@id":"https:\/\/www.systemcodegeeks.com\/#\/schema\/person\/967da353d0f1a1de21c9504942625a5f"},"headline":"Common Table Expressions in PostgreSQL","datePublished":"2016-09-28T14:15:17+00:00","dateModified":"2017-12-04T14:06:39+00:00","mainEntityOfPage":{"@id":"https:\/\/www.systemcodegeeks.com\/databases\/postgresql\/common-table-expressions-postgresql\/"},"wordCount":1056,"commentCount":0,"publisher":{"@id":"https:\/\/www.systemcodegeeks.com\/#organization"},"image":{"@id":"https:\/\/www.systemcodegeeks.com\/databases\/postgresql\/common-table-expressions-postgresql\/#primaryimage"},"thumbnailUrl":"https:\/\/www.systemcodegeeks.com\/wp-content\/uploads\/2016\/01\/postgresql-logo.jpg","articleSection":["PostgreSQL"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.systemcodegeeks.com\/databases\/postgresql\/common-table-expressions-postgresql\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.systemcodegeeks.com\/databases\/postgresql\/common-table-expressions-postgresql\/","url":"https:\/\/www.systemcodegeeks.com\/databases\/postgresql\/common-table-expressions-postgresql\/","name":"Common Table Expressions in PostgreSQL - System Code Geeks - 2026","isPartOf":{"@id":"https:\/\/www.systemcodegeeks.com\/#website"},"primaryImageOfPage":{"@id":"https:\/\/www.systemcodegeeks.com\/databases\/postgresql\/common-table-expressions-postgresql\/#primaryimage"},"image":{"@id":"https:\/\/www.systemcodegeeks.com\/databases\/postgresql\/common-table-expressions-postgresql\/#primaryimage"},"thumbnailUrl":"https:\/\/www.systemcodegeeks.com\/wp-content\/uploads\/2016\/01\/postgresql-logo.jpg","datePublished":"2016-09-28T14:15:17+00:00","dateModified":"2017-12-04T14:06:39+00:00","description":"This article is part of our Academy Course titled PostgreSQL Database Tutorial. In this course, we provide a compilation of PostgreSQL tutorials that will","breadcrumb":{"@id":"https:\/\/www.systemcodegeeks.com\/databases\/postgresql\/common-table-expressions-postgresql\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.systemcodegeeks.com\/databases\/postgresql\/common-table-expressions-postgresql\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.systemcodegeeks.com\/databases\/postgresql\/common-table-expressions-postgresql\/#primaryimage","url":"https:\/\/www.systemcodegeeks.com\/wp-content\/uploads\/2016\/01\/postgresql-logo.jpg","contentUrl":"https:\/\/www.systemcodegeeks.com\/wp-content\/uploads\/2016\/01\/postgresql-logo.jpg","width":150,"height":150},{"@type":"BreadcrumbList","@id":"https:\/\/www.systemcodegeeks.com\/databases\/postgresql\/common-table-expressions-postgresql\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.systemcodegeeks.com\/"},{"@type":"ListItem","position":2,"name":"Databases","item":"https:\/\/www.systemcodegeeks.com\/category\/databases\/"},{"@type":"ListItem","position":3,"name":"PostgreSQL","item":"https:\/\/www.systemcodegeeks.com\/category\/databases\/postgresql\/"},{"@type":"ListItem","position":4,"name":"Common Table Expressions in PostgreSQL"}]},{"@type":"WebSite","@id":"https:\/\/www.systemcodegeeks.com\/#website","url":"https:\/\/www.systemcodegeeks.com\/","name":"System Code Geeks","description":"Operating System Developers Resource Center","publisher":{"@id":"https:\/\/www.systemcodegeeks.com\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.systemcodegeeks.com\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/www.systemcodegeeks.com\/#organization","name":"Exelixis Media P.C.","url":"https:\/\/www.systemcodegeeks.com\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.systemcodegeeks.com\/#\/schema\/logo\/image\/","url":"https:\/\/www.systemcodegeeks.com\/wp-content\/uploads\/2022\/06\/exelixis-logo.png","contentUrl":"https:\/\/www.systemcodegeeks.com\/wp-content\/uploads\/2022\/06\/exelixis-logo.png","width":864,"height":246,"caption":"Exelixis Media P.C."},"image":{"@id":"https:\/\/www.systemcodegeeks.com\/#\/schema\/logo\/image\/"},"sameAs":["https:\/\/www.facebook.com\/systemcodegeeks","https:\/\/x.com\/systemcodegeeks"]},{"@type":"Person","@id":"https:\/\/www.systemcodegeeks.com\/#\/schema\/person\/967da353d0f1a1de21c9504942625a5f","name":"Gabriel Canepa","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.systemcodegeeks.com\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/27b3ea2a3fb1de4ed1c8694a1465c099a86586d8b833a0d852a26d76d750df9f?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/27b3ea2a3fb1de4ed1c8694a1465c099a86586d8b833a0d852a26d76d750df9f?s=96&d=mm&r=g","caption":"Gabriel Canepa"},"description":"Gabriel Canepa is a Linux Foundation Certified System Administrator (LFCS-1500-0576-0100) and web developer from Villa Mercedes, San Luis, Argentina. He works for a worldwide leading consumer product company and takes great pleasure in using FOSS tools to increase productivity in all areas of his daily work. When he's not typing commands or writing code or articles, he enjoys telling bedtime stories with his wife to his two little daughters and playing with them, the great pleasure of his life.","sameAs":["http:\/\/www.gabrielcanepa.com.ar\/","https:\/\/www.facebook.com\/gacanepa","https:\/\/ar.linkedin.com\/in\/gacanepa","https:\/\/x.com\/gacanepa"],"url":"https:\/\/www.systemcodegeeks.com\/author\/gabriel-canepa\/"}]}},"_links":{"self":[{"href":"https:\/\/www.systemcodegeeks.com\/wp-json\/wp\/v2\/posts\/1838","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.systemcodegeeks.com\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.systemcodegeeks.com\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.systemcodegeeks.com\/wp-json\/wp\/v2\/users\/15"}],"replies":[{"embeddable":true,"href":"https:\/\/www.systemcodegeeks.com\/wp-json\/wp\/v2\/comments?post=1838"}],"version-history":[{"count":0,"href":"https:\/\/www.systemcodegeeks.com\/wp-json\/wp\/v2\/posts\/1838\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.systemcodegeeks.com\/wp-json\/wp\/v2\/media\/198"}],"wp:attachment":[{"href":"https:\/\/www.systemcodegeeks.com\/wp-json\/wp\/v2\/media?parent=1838"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.systemcodegeeks.com\/wp-json\/wp\/v2\/categories?post=1838"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.systemcodegeeks.com\/wp-json\/wp\/v2\/tags?post=1838"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}