Ok, so you’ve stored hierarchical data in a relational database, and written recursive CTEs to query the data and find relationships. Now the application team wants to query hierarchical levels and print the complete ancestry tree. Time to deep dive into some advance CTE constructs.
This is the second post of the series about the Recursive SQL for querying hierarchical data started in the previous post . If you haven’t read it already, I recommend reading it to understand the key concepts:
What is a hierarchical data?
How to store hierarchical data in a relational database?
And how to query hierarchical data using:
Common Table Expressions (CTE)
In this post, we’ll discuss the advanced scenarios like displaying hierarchical levels and printing the “ancestry tree”. Let’s dive in…
Recently, I was working on an application that required reading hierarchically structured data. And I thought it might be useful to document multiple ways to store and query such hierarchical data (ex. Org. chart, File-system layout, or Set of tasks in a project) in a database. So, let’s jump right in.
Definitions first – what is hierarchical data?
Hierarchical data is a specific kind of data, characterized by a hierarchical relationship between the data sets.
Think about data sets having multiple levels: something above, something below, and a few at the same level. A typical example of such hierarchical model is an organizational chart like the one below.
Most modern-day relational database systems use SQL MERGE (also called UPSERT) statements to INSERT new records or UPDATE existing records if a matching row already exists. UPSERT is a combination of Insert and Update, driven by a “PRIMARY KEY” on the table.
SQL injection is a code injection technique that is capable of destroying your database. It is a code injection technique, used to attack data-driven applications, in which malicious SQL statements are inserted into an entry field for execution. (e.g. to dump the database contents to the attacker)
A transaction is a unit of work that you want to treat as “a whole.” It has to either happen in full or not at all. In Go lang, a transaction is essentially an object that reserves a connection to the database. You begin a transaction with a call to db.Begin() and close it with a commit() or rollback() method on the resulting Tx variable.
In the last post Updating/Deleting rows with Go, we learned to manipulate rows in PostgreSQL database in Go project using database/sql package that ships with Go, along with github.com/lib/pq Postgres driver. In this post, we’ll learn how to query rows i.e. SELECT
The Go programming language, sometimes referred to as Go lang, is making strong gains in popularity. Chances are if you are a Go developer, you will have to interact with SQL at some point in your project. This blog post will show how to connect to a PostgreSQL database from Go using database/sql package.