PostgreSQL

Recursive Query in Postgres

How can we write recursive query ?

Lets suppose a case when you have to find all the child data of the given Parent and you don’t want need access java code or functions the recursive query would be the best option. You can write a simple query to find all records on basis of single id in one table. How can you do that ? In JAVA can use recursion and in postgreSQL you can use recursive query.These type of queries mainly used for self joins. Here you must define your base case to exit the query loop. The main benefit of recursive query it eliminate boiler plate code and time on programming side.
Below is an example of recursive query.

testpk column is used as foreign key in same table test for column pk. You can find all the relations in table having pk 1 then the query is given below. Here the base case is pk=’1′ which will help the query to exit from recursion.

Here we have started query with with clause. With clause acts as a temporary tables which are created during the runtime.

Please follow and like us:

Post Author: Ambrish Rajput

Leave a Reply

Your email address will not be published. Required fields are marked *