How to store rich enum type (variants hold data) in Postgres?

[felix-andreas]

Hi! We would like store an enum type where the variants hold data in our Postgres database. For example:

enum Animal {
    Cat { name: String },
    Dog { age: i32 },
}

For now we created three tables animals, cats, and dogs. But this makes our queries very cumbersome:

#[derive(Debug, Deserialize, Serialize, sqlx::FromRow)]
struct AnimalDbJoined {
    id: Uuid,
    cat_name: Option<String>,
    dog_age: Option<i32>,
}

async fn list_animals(pool: &PgPool) -> anyhow::Result<Vec<Animal>> {
    let rows = sqlx::query_as!(
        AnimalDbJoined,
        r#"
            select animals.id as id, cats.name as cat_name, dogs.age as dog_age from animals
            left join cats on cats.id = animals.id
            left join dogs on dogs.id = animals.id
            where cats.id is not null or dogs.id is not null
        "#,
    )
    .fetch_all(pool)
    .await?;
    let animalas = rows
        .into_iter()
        .map(|row| match row {
            AnimalDbJoined {
                id: _,
                cat_name: Some(name),
                dog_age: None,
            } => Animal::Cat { name },
            AnimalDbJoined {
                id: _,
                cat_name: None,
                dog_age: Some(age),
            } => Animal::Dog { age },
            _ => panic!("Hardcore programming error!"),
        })
        .collect()
    Ok(animals)
}

Furthermore, this probably scales very bad. Is somebody aware of a better alternative to represent sum type (enums) in Postgres. Or, is there a way to automate this approach with sqlx? Any help is appreciated! Thanks!

[kulame]

Did you find a solution? I had the same problem

[felix-andreas]

No. We decided to store the content of the variants as JSON. I guess the concept of a sum type just doesn't fit very well into a SQL database.

I found this article very helpful for explaining the trait-offs of different approaches.