I have this kind of data that I need to store in postgres (ruby object).

    @description="Disallow user",
    @title="Disallow user">,
    @description="Restriction message",
    @title="Restriction message">,
    @description="List of configurations",
          @description="configuration name",
          @title="Profile Name">,
          @description="server URL",
          @description="Use Web logon",
          @title="Use Web logon for authentication">],

The ParsedRes can have multiple nested_res that contains and a list of ParsedRes. It can be nested up to two levels deep.

I'm wondering what will be the best design for this kind of data. I would to need to be able to fetch the same tree to display it in the future.

I thought about having a table that can link to a parent and daughter

CREATE TABLE parsed_res (
  id           SERIAL PRIMARY KEY,
  -- other columns
  parent_id    INT,
  daughter_id  INT
  1. Is it a good idea?
  2. What is the best way to fetch this tree (subselect?)
  3. ltree ?

1 Answer 1


Two methods of querying hierarchical data:

  1. Ltree. You can see an example of me using it in a similar question here.. The problem with ltree is you have to serialize the relationship yourself. It's a flat table. This is a pita. Updating it also kind of sucks.
  2. Self-referencing table. You can see an example of me using it in a similar question here.. This is probably ideal because you're only storing the identifier of the parent. This create a simple hierarchical data type that you can query with a RECURSIVE CTE.

Example table,

  idfoo          serial    PRIMARY KEY
  idparent       int       REFERENCES foo
  description    text,
  key            text,
  restriction    text,
  title          text,
  default        text

Then you insert your root, and your children which point to the parent.

  • Thanks a lot. I will need to be able to update part of a nested record and display it with hierarchy, ltree doesn't fit, will choose self-referencing table.
    – Mio
    Commented Apr 4, 2017 at 18:37
  • 1
    Note: you can update rows when you have ltree structure. The updating issue refers when you need to update the actual ltree column, e.g. when you want to move a node from under one parent to another. Commented Apr 4, 2017 at 22:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.