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
Processing graphs with recursive CTEs
24. Graphs in computer science
Summary

Instruction

Nice work! Since you already know how to tackle hierarchical structures represented in the form of trees, it's time to move on and introduce the second type of real-world situation we can tackle with recursive CTEs: graphs

Graphs consists of vertices and edges. In real life, graphs can often correspond to road maps. Take a look:

recursion

In a database, we may store vertices (cities) in one table and edges (roads between cities) in another one:

city
name
London
Cambridge
...
road
name_from name_to distance_km
London Cambridge 78
... ... ...

For educational purposes, we will simplify things a bit. We will only use a single table called city that stores city names and the revelant GPS coordinates. We'll assume that you can go directly from each city to another.

First, let's take a look at this table.

Exercise

Select all data from the table city. It contains the following columns:

  • name – The name of the city.
  • lat – The latitude of the city.
  • lon – The longitude of the city.

The last two columns indicate the GPS coordinates. Based on those coordinates, we will calculate the distance between two cities (in a straight line) and treat it as the distance.