On Fri, 19 May 2023 at 12:44, Dominique Devienne <ddevie...@gmail.com> wrote:
> On Thu, May 18, 2023 at 2:28 PM Victor Nordam Suadicani < > v.n.suadic...@gmail.com> wrote: > >> Is there any nice way to handle sum types (aka tagged unions) in a >> PostgreSQL database? [...] >> > A third method would be to save all fields of all variants into a single >> table, with all fields being nullable. >> > So you'd have a nullable text field, nullable integer and nullable double >> precision field. >> > > Yes, we do that. That's IMHO the only sane way to do it. > And if some of those alternatives are FKs (relations), that's the only > choice. > > You'd then need an additional tag field to indicate which variant of the >> union is used >> > > No, you don't need it. That's implicit from the NULL'ability of the > alternative columns. > If you want, you can have it as a generated column, thus read-only. > Worse, having it as an explicit column would make it denormalized, and > possibly out of sync. > > >> and you'd have to write check constraints for each variant to ensure that >> all the fields in that variant are not null and all the fields not in that >> variant *are* null. >> > > Yes indeed. > > >> This *almost* works, but has two major problems: >> >> 1. It wastes space. In Rust, an enum is only as big as its largest >> variant. Using this method, a table row would be as big as the sum of all >> the variants. >> > > Not really, or not to a point it matters that much. > I don't know about the actual physical bytes on disk for PostgreSQL, but > as an example in SQLite, > all columns have *at least* 1 "header" byte per value, and NULL values > (and 0 and 1) have no "payload" bytes. > In PostgreSQL (which is more "rigidly typed" as DRH would say :)) you may > waste space for primitive types, > but not for text and bytea, which is where it really matters IMHO. > > >> 2. Querying the data is very cumbersome, [...]. >> > > Sure, it's cumbersome. But I don't get your point here. NULL handling is > part of SQL. > And sum-types (variants) implemented via exclusive NULL'ness is just a > special case. > You "dispatch" to the proper column on writes. You read all alternatives > and assign the one (if any) NOT NULL to the variant. > > >> Both of these problems get bigger and bigger as you add more variants - >> it doesn't scale well. >> > > ORMs cannot magically resolve the impedence mismatch between SQL and > OO-based or sum-type based type systems a la Rust (and co). > If you need SQL, you need to design for SQL for the get go. Not shoehorn > your Rust data model into SQL. > > My $0.02. > Thanks for the perspective :) > If you need SQL, you need to design for SQL for the get go. Not shoehorn your Rust data model into SQL. Sometimes the data in the domain really does fit a sum type and then a sum type is the right tool to use (whether you use Rust or Haskell or whatever language). Trying to shoehorn your domain data model into a data format that doesn't fit isn't the way to go either. I feel like it's a deficiency in SQL that there is no support for sum types. I would guess this is influenced by the fact that SQL was developed in a time when there were no major programming languages with sum type support either. But really it's not that I "need" SQL per se, it's just that SQL databases are the most developed and used at this time. Do you know of any other production-grade databases that actually support sum types in a better way than SQL? I'd be very curious cause I haven't really found any.