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?

You might want to also show some sample data and queries, so we know exactly how you plan to use the `hierarchyid` field. — Tim Biegeleisen, Jan 31 '21 at 07:26

score 2 · Answer 1 · edited Mar 15 '23 at 21:20

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:

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

What is the equivalent datatype for SQL Server hierarchyid in PostgreSQL

1 Answers1