It depends on your tolerance for the exact feature set that you get. The JSON type can be pressed into service to get something that represents that, in the sense that what you are asking for is a subset of the valid JSON values that Postgres can store. If you just want to slam the values into some local tagged union in your language when querying them, then problem effectively solved. If you want to start operating on it in the database itself, you have a lot more quirky things to work through. If you want the semantics inside Postgres to be exactly what a tagged union/sum type looks like then I think you'd be looking at a custom data type at a minimum, and even then there are probably limits as to how close you could get inside.

One of the restrictions of composite types is that they can not contain an instance of themselves. So unfortunately, this is not currently possible.

I had this issue when trying to implement an AST type for pg_graphql[1] back when it was written in SQL [2]. In the end we used a JSON type which was much less constrained. That might be solvable using pg_jsonschema [3] if you really wanted to have a good time though

[1] https://github.com/supabase/pg_graphql

[2] https://github.com/supabase/pg_graphql/blob/34cc266da972d356...

[3] https://github.com/supabase/pg_jsonschema

What's your opinion between jsonb vs hstore columns? https://www.postgresql.org/docs/15/hstore.html

IIRC, hstore pre-dated jsonb. I'm not aware of any performance differences for the object type but it would be worth checking if you have a heavy workload. jsonb will tend to have better ORM support (if thats something you care about) and it also isn't behind an extension which is a (small) perk

Actually, another thing. JSON works. But what about doing pattern matching on the tagged enum? Each variant should give access to its fields

Can https://www.postgresql.org/docs/15/functions-conditional.htm... be used to do it?

While that might not fit your needs I map unions to Postgres with an enum encoding the type and then adding constraints on which additional columns must be set (and also must not be set) depending on the type. For your example the enum might be leaf or tree and then if it’s of type tree you have non nullable left_child and right_child columns and if it’s a leaf those must be null, and then you might have a value column that is non null in both cases or only for leaves.

This is my pet peeve with SQL. I hope some day PostgreSQL gain support for native tagged unions.

I have saved some links about this

https://github.com/solidsnack/pg-sql-variants

https://thelyfsoshort.io/variant-types-in-postgresql-c63725f...

https://www.parsonsmatt.org/2019/03/19/sum_types_in_sql.html

https://typeable.io/blog/2019-11-21-sql-sum-types.html

Right now I think the ideal is a jsonb column (with a field that stores the tag) plus https://github.com/supabase/pg_jsonschema. But this is only usable if your language translates to/from JSON and generates json schema for you.

Rust can do this with https://serde.rs/ and https://docs.rs/schemars/latest/schemars/