sqlhydra-npgsql generation tool doesn't pick up "enum" columns

[kurt-mueller-osumc]

Postgres has enumerated datatypes. The sqlhydra-npgsql generation tool doesn't seem to pick up these type of columns. I'd love it if it could at least think it's a column of type string.

[JordanMarr]

That sounds doable as long as there is a way to query the custom types. It may be possible through the NpgslConnection GetSchema method; otherwise, we will need a query to get it.

[JordanMarr]

I got it working already (with strings). It would cool to have it generate a discriminated union for each enum type, but that's for another day. Will let you know when this is ready on NuGet.

[JordanMarr]

SqlHyrda.Npgsql v0.800.1 is published. Let me know if that causes any issues with inserts/updates.

[JordanMarr]

I assume this is resolved so closing.

[EverybodyKurts]

Sorry, I should have gotten back to you sooner... I get an Npgsql.PostgresException when attempting to insert a column:

Npgsql.PostgresException (0x80004005): 42804: column "color" is of type colors but expression is of type text

Basically, any string being inserted needs to be typecasted to the proper enum type like the following:

SELECT 'blue'::colors;

EDIT : Changed "blue" to 'blue'

[JordanMarr]

Any chance that it could it be a case-sensitivity issue?

[EverybodyKurts]

I think I would have gotten a different error had it been a case-sensitivity issue:

db=# CREATE TYPE colors AS ENUM ('red', 'green', 'blue');
CREATE TYPE

db=# SELECT 'Blue'::colors;
ERROR:  invalid input value for enum colors: "Blue"
LINE 1: SELECT 'Blue'::colors;

db=# SELECT 'blue'::colors;
 colors 
--------
 blue
(1 row)

[EverybodyKurts]

I don't know if this helps or not, but here's me creating a table and inserting a row from the psql command line:

db=# CREATE TABLE cars (color colors);
CREATE TABLE

db=# INSERT INTO cars (color) VALUES ('blue');
INSERT 0 1

db=# SELECT * FROM cars;
 color 
-------
 blue
(1 row)

[JordanMarr]

Ok I see what needs to happen:

        testTask "Insert Enum ADONET" {
            use conn = new Npgsql.NpgsqlConnection(connectionString)
            use cmd = conn.CreateCommand()
            cmd.CommandText <- 
                """
                INSERT INTO experiments.person (name, current_mood)
                VALUES (@name, @mood::mood)
                """
            let pName = cmd.CreateParameter()
            pName.ParameterName <- "@name"
            pName.Value <- "john doe"
            cmd.Parameters.Add pName |> ignore

            let pMood = cmd.CreateParameter()
            pMood.ParameterName <- "@mood"
            pMood.Value <- "happy"
            cmd.Parameters.Add pMood |> ignore

            conn.Open()

            let! results = cmd.ExecuteNonQueryAsync()

            Expect.isTrue (results > 0) ""
        }

[JordanMarr]

This is going to be really cool... 😎

[JordanMarr]

New release! https://github.com/JordanMarr/SqlHydra/releases/tag/v0.810.0

You will need to manually register the generated enums with Npgsql (at least for now).

[JordanMarr]

Added a caveat that you may need to watch out for in the release notes. I filed an npgsql issue.