Postgres can do recursive common-table expressions if you really want, but it’s worth considering carefully exactly how your application will use the nested categories.

• how often are new categories added? Some representations will make this expensive
• how often do queries like “in this category and all its subcategories” occur? Some representations will make THAT expensive

Poking around in Hex turns up some candidates:

• Arbor uses CTEs to traverse parent_id chains
• Ancestry uses materialized paths; each record stores the full “path” through the hierarchy (a record in Subsubcategory D would store “A/C/D” or similar)
• as_nested_set uses nested sets to make answering “and all subcatgories” questions faster
• Hierarch linked up-thread by @fuelen uses a Postgres extension to offload some of the complexity to the database

Hello

Did you see this library GitHub - Byzanteam-Labs/hierarch: Tree structure & hierarchy for ecto models with ltree(Postgres) · GitHub?
It is based on ltree postgres extension. I didn’t try it, but I’d try if I have this kind of requirements

Another way is to use a nested set model, using lft and rgt additional columns.

https://www.sciencedirect.com/topics/computer-science/nested-set-model