How to add parameter for postgres enum types in C#

Question

Hi currently have the problem, that I want to insert values in a postgres database where the table contains self defined types like.

CREATE TYPE TestEnum AS ENUM ('Value1','Value2');

When I try to add a parameter in C# I always get an error because of the wrong NpgsqlDbType. So my question is what NpgsqlDbType to use for such self defined Types.

var parameter = new NpgsqlParameter(":p1", NpgsqlDbType.????)
{
    Value = "Value1",
    Direction = ParameterDirection.Input
}

Thanks for your help. I'm really going mad because of this problem.

Answer 1

According to PostgreSQL 8.4.4 Documentation: 8.7. Enumerated Types:

An enum value occupies four bytes on disk.

That suggests that they are internally stored as a 32-bit integer. It uses the system catalog pg_enum to map from integers to the names and back.

Unfortunately, the documentation for NpgsqlDbType does not elucidate the meaning of each of those enum values, but my guess would be that NpgsqlDbType.Integer is likely to refer to a 32-bit integer.

However, I must confess that I am guessing here. It is also possible that NpgsqlParameter expects a type that represents the data you send to the DB with the query, which is clearly a string, so if NpgsqlDbType.Integer doesn’t work, my next guess would be NpgsqlDbType.Varchar.

Answer 2

After all, I found a solution that solves the problem, although it's not the real solution. I now add a NpgsqlDbType.Varchar parameter and add a CAST(:p1 as "TestEnum") to the SQL

e.g.

INSERT INTO tableName (Col) VALUES ( CAST(:p1 as "TestEnum") )

It works for me, although I do not think that this is a very nice solution due to the cast. If someone will find a better solution in future, please drop me a line. ;)