Deals Of The Week - hours only!Up to 80% off on all courses and bundles.-Close

Wrap course as a gift

Prepare your gift and double-check it looks just right.

Form validation errors

Recipient's Name:
Recipient's Email:
Your Message (Optional):
Proceed to Checkout

Recursive Queries in PostgreSQL

Introduction
The very basics
Infinite looping
11. LIMIT keyword
Processing graphs with recursive CTEs
Summary

Instruction

As you saw, our query terminated with an error – but that's only because of the special database settings in our course. Normally, the database would not stop the query on its own. As a result, it would freeze and you would have to break the query manually. Please remember that!

In PostgreSQL, you can use another keyword, LIMIT n (where n is the maximal number of rows you expect) to stop the query. Even if your query does not contain a termination check (or it does, but the check is faulty), LIMIT will take care of query termination:

WITH RECURSIVE counter(previous_value) AS (
  SELECT
    1 -- anchor member
  UNION ALL
  SELECT
    previous_value + 1 -- recursive member
  FROM counter
)

SELECT 
  * 
FROM counter -- invocation
LIMIT 10; -- termination check

Exercise

Run the template query with LIMIT and see for yourself how it works.