unsupported type ResourceKind for param #2

[kulame]

Bug Description

my table

CREATE TYPE "ResourceKind" AS ENUM ('MOVIE', 'BOOK');
CREATE TABLE "resources" (
    "id" SERIAL NOT NULL,
    "kind" "ResourceKind" NOT NULL DEFAULT 'BOOK',
    CONSTRAINT "resources_pkey" PRIMARY KEY ("id")
);

    let rec = sqlx::query!(
        r#"
            insert into resources(kind) values($1)
            returning id
        "#,
        kind,
        Local::now().naive_local()
    )
    .fetch_one(pool)
    .await
    .map_err(|_| ServerError::DatabaseFault)?;

it tell me

unsupported type ResourceKind for param #2rustc
mod.rs(319, 9): Actual error occurred here
mod.rs(319, 9): Error originated from macro call here

how can i insert a postgres enum type?

[kulame]

#1171 1171

[VersBinarii]

But in your case you should be able to do

let rec = sqlx::query!(
        r#"
            insert into resources(kind) values($1)
            returning id
        "#,
        kind as _,
    )
    .fetch_one(pool)
    .await
    .map_err(|_| ServerError::DatabaseFault)?;

to get it to work

[mike-lloyd03]

I'm running into this same error but I'm not sure it's related to #1171. My enum is defined inside the same schema as the rest of the database.

CREATE TYPE "role" AS ENUM (
  'user',
  'manager',
  'admin'
);

CREATE TABLE "users" (
  "id" BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  "username" VARCHAR NOT NULL UNIQUE,
  "first_name" VARCHAR,
  "middle_name" VARCHAR,
  "last_name" VARCHAR,
  "email" VARCHAR NOT NULL UNIQUE,
  "role" role NOT NULL,
  "active" BOOLEAN NOT NULL,
  "password_hash" VARCHAR
);

In my code I have:

#[derive(Debug, Default, Clone, Serialize, Deserialize, Type, PartialEq, Eq)]
#[sqlx(rename_all = "snake_case")]
#[sqlx(type_name = "role")]
#[serde(rename_all = "snake_case")]
pub enum Role {
    #[default]
    User,
    Manager,
    Admin,
}


#[derive(Debug, Default, Serialize, Deserialize, FromRow)]
pub struct User {
    #[serde(skip_deserializing)]
    pub id: i64,
    pub username: String,
    pub first_name: Option<String>,
    pub middle_name: Option<String>,
    pub last_name: Option<String>,
    pub email: String,
    pub role: Role,
    pub active: bool,
    password_hash: Option<String>,
}

impl User {
    async fn get_by_username(pool: &PgPool, username: String) -> Result<Self, sqlx::Error> {
        sqlx::query_as!(Self, "SELECT * FROM users WHERE username = $1", username)
            .fetch_one(pool)
            .await
    }
}

Which raises the error:

error: unsupported type role of column #7 ("role")

The only way around this is the much more verbose:

    async fn get_by_username(pool: &PgPool, username: String) -> Result<Self, sqlx::Error> {
        sqlx::query_as!(
            Self,
            r#"
                SELECT 
                    id,
                    username,
                    first_name,
                    middle_name,
                    last_name,
                    email,
                    role as "role: Role",
                    active,
                    password_hash
                FROM users WHERE username = $1
            "#,
            username
        )
        .fetch_one(pool)
        .await
    }

[axeld-galadrim]

@abonander maybe close it as https://github.com/launchbadge/sqlx/issues/2149#issuecomment-1301844692 solves it ?

[alissa-tung]

I am confusing about why when using template data, the as _ is needed, and when returning data, role as "role: Role" is needed. Is there any document for this behavior?