EDUCBA Logo

EDUCBA

MENUMENU
  • Explore
    • EDUCBA Pro
    • PRO Bundles
    • Featured Skills
    • New & Trending
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Log in
  • Sign Up
Home Data Science Data Science Tutorials PostgreSQL Tutorial PostgreSQL Recursive Query
 

PostgreSQL Recursive Query

Priya Pedamkar
Article byPriya Pedamkar

Updated May 11, 2023

PostgreSQL Recursive Query

 

 

Definition of PostgreSQL Recursive Query

PostgreSQL recursive query provides with a statement which was allows us to use an auxiliary statement in the query. In PostgreSQL, a recursive query, also known as a recursive common table expression, is utilized in various scenarios, including querying hierarchical data such as organizational structures. In PostgreSQL, common table expressions are similar to temporary tables, and they are used during query execution. This will provide a recursive query, which is recursive in nature, and it will fetch the data until the termination condition was not met.

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

Syntax:

The below syntax shows a recursive query.

WITH RECURSIVE (common table expression name) or table_name AS(
Definition of non-recursive term common table expression
UNION or UNION ALL
Definition of recursive term common table expression
) SELECT * FROM table_name or common table expression name;

Parameter:

Below is the parameter description syntax of the recursive query.

  • Recursive: Using recursive query and with clause, we are creating temporary tables; the scope of this temporary table is only that query itself.
  • CTE name or table name: Common table expression name is specified in its table name on which we have retrieving data.
  • Non-recursive term: The PostgreSQL common table expression query defines the result set of common table expressions.
  • Recursive term: In PostgreSQL, we can define it by joining one or more common table expressions or query definitions with a non-recursive term.
  • UNION: In PostgreSQL, we can use union and union all to join two queries using the recursive query.
  • Termination check: We use it when the recursion has reached its end and there are no more rows to return from the iteration.
  • Select: We use it to select data from a table or common table expressions in PostgreSQL.

How Recursive Query Works in PostgreSQL?

  • Below is the working of a recursive query in PostgreSQL.
  • Recursive query in PostgreSQL will work in three parts; the recursive query has four basic steps to execute the query. PostgreSQL will execute the common table expression or recursive query in the following sequence are as follows.
    • We define it as the initial value and populate the working table of the recursive query in this step.
    • The second step is known as the recursive part. As we can see, the recursive query will referring to print the numbers itself, and it will get the values from the working table, which we have mentioned in the recursive query in PostgreSQL. This recursive function query will continue running until the termination condition is met. It executes the recursive result with Ri as input, and it will result as Ri+1.
    • The third step involves executing the actual query to select all values from a table column or common table expressions. This step is repeated until the set is empty, as defined in the second step. It is also called a termination check.
    • The fourth step is to return the final result using the UNION and UNION ALL statement.
  • The recursive common table expressions and subordinates will define the one non-recursive and one recursive term.
  • The non-recursive term will return the base result set of R0. The recursive term will return the direct subordinates.
  • The statement in PostgreSQL will provide a way to write an auxiliary statement for use in the large query.
  • A common use for common table expressions is as auxiliary statements. Each of these auxiliary statements can select, update, delete, or insert data, and then the main statement can reference them using the WITH clause.
  • Recursive query evaluation will contain the non-recursive term’s evaluation; for using UNION, we can discard the duplicate rows.
  • The form of a recursive statement with a query in PostgreSQL is always a non-recursive statement, where the recursive statement contains the reference of the queries output.

Examples

We have using the student table to describe an example of a recursive query. Below is the student table structure, and the data that we have used in the example are as follows.

select * from student;
\d+ student;

PostgreSQL Recursive Query-1.1

Example #1 – Recursive Query using UNION

The below example shows that recursive query using UNION.

Code:

WITH RECURSIVE subordinates AS (
SELECT
stud_id,
phone,
stud_name
FROM
student
WHERE
stud_id = 10
UNION
SELECT
s.stud_id,
s.phone,
s.stud_name
FROM
student s
INNER JOIN subordinates sub ON sub.stud_id = s.phone
) SELECT
*
FROM
subordinates;

Output:

PostgreSQL Recursive Query-1.2

  • In the above example, the recursive common table expression defines the recursive and non-recursive term.
  • Non-recursive term will return the base result of R0, which was the stud_id is 10.

Example #2 – Recursive Query using UNION ALL

  • The below example shows that recursive query using UNION ALL.
  • The non-recursive term will return the base result of R0, which was the stud_id is 5.

Code:

WITH RECURSIVE subordinates AS (
SELECT
stud_id,
phone,
stud_name
FROM
student
WHERE
stud_id = 5
UNION ALL
SELECT
s.stud_id,
s.phone,
s.stud_name
FROM
student s
INNER JOIN subordinates sub ON sub.stud_id = s.phone
) SELECT
*
FROM
subordinates;\

Output:

recursive queries using UNION

Example #3 – Recursive Query to Retrieve All Data from Table

The below example shows that recursive queries using UNION to retrieve all data from the table.

Code:

WITH RECURSIVE subordinates AS (
SELECT
stud_id,
phone,
stud_name
FROM
Student
UNION
SELECT
s.stud_id,
s.phone,
s.stud_name
FROM
student s
INNER JOIN subordinates sub ON sub.stud_id = s.phone
) SELECT
*
FROM
subordinates;

Output:

recursive queries using UNION

Recommended Articles

We hope that this EDUCBA information on “PostgreSQL Recursive Query” was beneficial to you. You can view EDUCBA’s recommended articles for more information.

  1. PostgreSQL Variables
  2. PostgreSQL round
  3. GRANT PostgreSQL
  4. PostgreSQL Auto Increment

Primary Sidebar

Footer

Follow us!
  • EDUCBA FacebookEDUCBA TwitterEDUCBA LinkedINEDUCBA Instagram
  • EDUCBA YoutubeEDUCBA CourseraEDUCBA Udemy
APPS
EDUCBA Android AppEDUCBA iOS App
Blog
  • Blog
  • Free Tutorials
  • About us
  • Contact us
  • Log in
Courses
  • Enterprise Solutions
  • Free Courses
  • Explore Programs
  • All Courses
  • All in One Bundles
  • Sign up
Email
  • [email protected]

ISO 10004:2018 & ISO 9001:2015 Certified

© 2025 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA Login

Forgot Password?

🚀 Limited Time Offer! - 🎁 ENROLL NOW