3 ms·

Any idea of how to implement tagged unions in Postgres. For example, Tree a = Leaf a | Branch (Tree a, Tree a) This is one data type feature which would be gre

• csnweb 4y ago
  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.
• nextaccountic 4y ago
  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://github.com/solidsnack/pg-sql-variants https://thelyfsoshort.io/variant-types-in-postgresql-c63725f8678b https://thelyfsoshort.io/variant-types-in-postgresql-c63725f... https://www.parsonsmatt.org/2019/03/19/sum_types_in_sql.html https://www.parsonsmatt.org/2019/03/19/sum_types_in_sql.html https://typeable.io/blog/2019-11-21-sql-sum-types.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 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/ https://serde.rs/ and https://docs.rs/schemars/latest/schemars/ https://docs.rs/schemars/latest/schemars/
• oliverrice 4y ago
  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 https://github.com/supabase/pg_graphql [2] https://github.com/supabase/pg_graphql/blob/34cc266da972d356365f953503deb7a39960da0a/pg_graphql--0.4.0.sql https://github.com/supabase/pg_graphql/blob/34cc266da972d356... [3] https://github.com/supabase/pg_jsonschema https://github.com/supabase/pg_jsonschema
  • nextaccountic 4y ago
    What's your opinion between jsonb vs hstore columns? https://www.postgresql.org/docs/15/hstore.html https://www.postgresql.org/docs/15/hstore.html
    • oliverrice 4y ago
      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 and it also isn't behind an extension which is a (small) perk
  • nextaccountic 4y ago
    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.html https://www.postgresql.org/docs/15/functions-conditional.htm... be used to do it?
• jerf 4y ago
  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.