Re: Is there a good way to handle sum types (or tagged unions) in PostgreSQL?

On Fri, 19 May 2023 at 12:44, Dominique Devienne <ddevienne(at)gmail(dot)com>
wrote:

> On Thu, May 18, 2023 at 2:28 PM Victor Nordam Suadicani <
> v(dot)n(dot)suadicani(at)gmail(dot)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.