Good enough ten-second sum types for Postgres

I understood the motivation. But you set the default on the new column already, so that should cover it. I’m not clear what’s gained by the extra field.

I like the idea for sum types like this, I just don’t see the appeal in the example ;)

One could add a constraint CHECK (filled_at IS NOT NULL) NOT VALID

JFYI PostgreSQL 18 is gonna support NOT VALID for NOT NULL constraints, so the CHECK workaround would not necessary anymore.

I want the timestamp to be NOT NULL on all new records without fussing with it on old ones.

The thing here is, your enum doesn’t prevent this either, you can still insert things without a timestamp

Occasionally I see people making SQL pre-processors to add features to the language; what I’d really love is a preprocessor that turns something like:

CREATE ENUM shape (
    square(side NUMBER),
    circle(radius NUMBER),
);

…and generates a table with all the fields and CHECKs implied:

CREATE TABLE shape (
    _tag TEXT NOT NULL CHECK (_tag = 'square' OR _tag = 'circle'),
    side NUMBER,
    radius NUMBER,
    CHECK (
        (_tag = 'square' AND side IS NOT NULL AND radius IS NULL) OR
        (_tag = 'circle' AND side IS NULL AND radius IS NOT NULL)
    )
)

It’s hard to put my finger on it but sum types don’t feel quite right in a relational model. If I had records that could be in different “varieties“ with extra columns, I would probably model that as multiple tables. One for all of the records, and then a table each for the variations. Of course, then you cannot make them mutually exclusive, unless you had something like an anti-foreign key constraint?

I don’t do enough DB wrangling to comment, but I like how straightforward and to-the-point this post is. (Even if the red-on-green hurts my eyes for whatever reason.)

Interested in other ways people handle this, even if they require more than ten seconds to think about.