What is the equivalent datatype for SQL Server hierarchyid in PostgreSQL

Question

I have a table in SQL Server which has a column of type hierarchyid, the issue is that I can't find the equivalent datatype for this in PostgreSQL.

CREATE TABLE dbo.exampleTable{
   id int;
   name varchar(255);
   level hierarchyid not null;
}

How would I write the equivalent in PostgreSQL?

Answer 1

There is no a direct equivalent datatype in PostgreSQL and without more information on your use case, as Tim correctly says in his comment it is difficult to provide a full answer.

You do have 2 options:

1. You may want to look at the ltree module in the documentation: https://www.postgresql.org/docs/13/ltree.html
2. Another way hierarchical data is modelled in PostgreSQL is using materialized views with recursive common table expressions.