Sqlx: enum any of array

I'm trying to select on a subset of enum variants, but getting "the trait PgHasArrayType is not implemented". Any suggestions?

//! ```cargo
//! [dependencies]
//! tokio = { version = "1", features = ["full"] }
//! dotenv = "0.15"
//! sqlx = { version = "0.6", features = ["runtime-tokio-native-tls", "postgres"] }
//! ```
use sqlx::Row;

#[derive(Debug, sqlx::Type)]
#[sqlx(type_name = "color")]
#[sqlx(rename_all = "lowercase")]
enum Color {
    Red,
    Green,
    Blue,
}

#[tokio::main]
async fn main() -> sqlx::Result<()> {
    dotenv::dotenv().ok();
    let db = sqlx::PgPool::connect(&std::env::var("DATABASE_URL").unwrap()).await?;
    let mut tnx = db.begin().await?;

    sqlx::query("CREATE TYPE color AS ENUM ('red', 'green', 'blue')")
        .execute(&mut tnx)
        .await?;
    sqlx::query("CREATE TABLE colors (color color)")
        .execute(&mut tnx)
        .await?;
    sqlx::query("INSERT INTO colors VALUES ('red'), ('green')")
        .execute(&mut tnx)
        .await?;

    /// Quering by a single enum works
    assert_eq!(
        sqlx::query("SELECT color FROM colors WHERE color = $1")
            .bind(Color::Red)
            .fetch_one(&mut tnx)
            .await?
            .get::<Color, &str>("color"),
        Color::Red
    );

    /// Error: the trait `PgHasArrayType` is not implemented for `Color`
    sqlx::query("SELECT color FROM colors WHERE color = ANY($1)")
        .bind(&[Color::Blue, Color::Red])
        .fetch_one(&mut tnx)
        .await?
        .get::<Color, &str>("color");

    tnx.rollback().await?;
    Ok(())
}

Try using a tuple here instead:

.bind((Color::Blue, Color::Red))

You can see in the trait documentation that it's implemented for tuples, but not &[T]: PgHasArrayType in sqlx::postgres - Rust

1 Like

I'm getting error[E0277]: the trait bound (Color, Color): Encode<'_, Postgres> is not satisfied. But anyway it won't help me as I'm looking for a way to compare with a random array. E.g. it works with strings:

    sqlx::query("SELECT color FROM colors WHERE color = ANY($1::color[])")
        .bind(&["blue", "red"])
        .fetch_one(&mut tnx)
        .await?
        .get::<Color, &str>("color");

I can use something like strum to automatically cast enum to string of course, I just thought there's a proper way, like I'm missing a trait or something