Error when inserting custom Enum

[mmarkmos]

I have the following SQL

CREATE TYPE foo_type AS ENUM ( 'foo',  'bar');

CREATE TABLE foo_count(
    id INTEGER ,
    foo foo_type;
    count        BIGINT,
    PRIMARY KEY (foo, id)
);

and the following rust code

#[derive(Debug, ToSql, FromSql, Eq, Hash, PartialEq, Copy, Clone)]
#[postgres(name = "foo_type")]
pub enum Foo {
     #[postgres(name = "foo")]
    FOO,
     #[postgres(name = "bar")]
    BAR,
}

When trying to insert with BinaryCopyInWriter, it works.

    let foo_type = Type::new(
            "foo_type".to_string(),
            0,
            Kind::Enum(vec![
                "foo".to_string(),
                "bar".to_string(),
                ]),
           "".to_string(),
   );

    let types: &[Type] = &[Type::INT4, foo_type, Type::INT8];
    let sink = client.copy_in("COPY foo_count (id, foo, count) FROM stdin BINARY;").await.unwrap();
    let writer = BinaryCopyInWriter::new(sink, &types);
    pin_mut!(writer);
    let row: Vec<&'_ (dyn ToSql + Sync)> = vec![&(1i32), &Foo::FOO, &(100i64)];
    writer.as_mut().write(&row).await;

    println!("Rows added {:?}", writer.finish().await);

But when trying to use a prepared Statement it doesn't work;

    let types: &[Type] = &[Type::INT4, foo_type, Type::INT8];
    let stmt: tokio_postgres::Statement = client.prepare_typed("INSERT INTO foo_count (id, foo, count) VALUES ($1, $2, $3);",    types).await.unwrap();

    let row: Vec<&'_ (dyn ToSql + Sync)> = vec![&(1i32),  &Foo::FOO, &(100i64)];
    client.execute(&stmt, &row).await.unwrap();

Getting the error

thread 'main' panicked at 'called `Result::unwrap()` on an `Err` value: Error { kind: ToSql(1), cause: Some(WrongType { postgres: Other(Other { name: "foo_type", oid: 18157, kind: Enum(["foo", "bar"]), schema: "public" }), rust: "all_in_rust:: Foo" }) }'

[sfackler]

You are setting the OID of the Type to 0, which is not correct. If you need to specify the type explicitly in a statement (which you do not in this case), you will want to retrieve the type from the database by e.g. preparing a statement that returns it and pulling the type from the returned Statement.

[mmarkmos]

Thank you for your quick response, but when getting the type from

client.query("select unnest(enum_range(null::foo_type));");

or statticly setting the oid to 18157 im am still getting the same error.

[sfackler]

This test passes for me.

#[test]
fn foo() {
    #[derive(Debug, ToSql, FromSql, Eq, Hash, PartialEq, Copy, Clone)]
    #[postgres(name = "foo_type")]
    pub enum Foo {
        #[postgres(name = "foo")]
        FOO,
        #[postgres(name = "bar")]
        BAR,
    }

    let mut conn = Client::connect("user=postgres host=localhost port=5433", NoTls).unwrap();
    conn.batch_execute(
        "
    CREATE TYPE pg_temp.foo_type AS ENUM ( 'foo',  'bar');

    CREATE TEMPORARY TABLE foo_count(
        id INTEGER ,
        foo foo_type,
        count        BIGINT,
        PRIMARY KEY (foo, id)
    );
    ",
    )
    .unwrap();

    let stmt = conn
        .prepare("INSERT INTO foo_count (id, foo, count) VALUES ($1, $2, $3)")
        .unwrap();

    conn.execute(&stmt, &[&1i32, &Foo::FOO, &100i64]).unwrap();
}

[mmarkmos]

Thank you again, for your swift help. Apparently I missed one enum in the sql, it was commented out 😅.

Interesting how the copy statement was still working.