Florent Jardin builds a hierarchy:
The SQL standard defines a set of rules so that database systems can be interchangeable, but there are small singularities in the wild. In this regard, the
hierarchyid
data type provided by SQL Server is a striking example. If you are switching to PostgreSQL, two solutions are available to you.A first and simpler solution consists in linking each node to its parent using a new
parentid
column and applying a foreign key constraint. Another, more complete approach consists in using theltree
extension. This article deals with the latter case.
Read on to learn more.