Is it possible to define recursive composite types in Postgres 13?

create type "t" as (
  "a" int,
  "b" t

I am aware that I can use a table with a parent-child relationship. The only disadvantage is that I will need to query each nested level instead of directly accessing the nested data.

I am also aware that I can use jsonb, but I am not sure if it can access the nested data directly without querying each nested level?

What are the other alternatives using PL/pgSQL only?

I need to access each nested level separately.

  • 1
    You're going to have to expand on your use case - recursive CTEs are all about accessing nested levels... perhaps a small fiddle (dbfiddle.uk) with some sample data and your desired output and maybe expand on your train of thought using the sample data as your exemplar? Also, please put the sample material into the question also...
    – Vérace
    May 16, 2021 at 9:50
  • For alternatives, you have to make your use case clear first. Start a new question for that. May 25, 2021 at 19:10

1 Answer 1


No, "recursive" type definitions are not possible in Postgres.

You cannot reference a type in CREATE TYPE that is not registered, yet.
(What would terminate the recursion anyway?)

  • NULL value in the field could terminate the recursion Feb 1, 2023 at 8:42

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.