Create Postgresql enum type and binary import with Npgsql

Question

Using Npgsql, I am trying to create a Postgresql enum type and then to do a binary import from .NET.

Project file:

<Project Sdk="Microsoft.NET.Sdk">
  <PropertyGroup>
    <OutputType>Exe</OutputType>
    <TargetFramework>net7.0</TargetFramework>
  </PropertyGroup>
  <ItemGroup>
    <PackageReference Include="Npgsql" Version="7.0.2" />
  </ItemGroup>
</Project>

Source file:

using Npgsql;

var connectionString =
  "Host=localhost;" +
  "Port=5432;" +
  "Username=postgres;" +
  "Password=postgres;" +
  "Database=test_db;";

using var connection = new NpgsqlConnection(connectionString);

connection.Open();

var cmd = connection.CreateCommand();

cmd.CommandText =
  "create type mood as enum ('happy', 'sad');" +
  "create table people (age integer, cmood mood);";

cmd.ExecuteNonQuery();

var copyCmd =
  "copy people (age, cmood)" +
  "from stdin (format binary)";

using var writer = connection.BeginBinaryImport(copyCmd);

writer.StartRow();
writer.Write(12);
writer.Write(Mood.Happy);
writer.Complete();

enum Mood
{
  Null = 0,
  Sad = 1,
  Happy = 2
}

Of course, a Postgresql server is running on my machine and an empty database test_db has been created. Executing the above program results in a System.ArgumentException ("A PostgreSQL type with the name mood was not found in the database"). However, querying the database from the command line shows that the type has been created exactly when expected, i.e. after executing cmd.ExecuteNonQuery() in the above listing.

Why is the binary import failing and how can I make it work?

Answer 1

Thanks to this answer to a related question, I ended up on this entity framework article about postgres enums where I found out that I needed a single line connection.ReloadTypes(); to make my program work!

Complete working program (just one line is different!):

using Npgsql;

var connectionString =
  "Host=localhost;" +
  "Port=5432;" +
  "Username=postgres;" +
  "Password=postgres;" +
  "Database=test_db;";

using var connection = new NpgsqlConnection(connectionString);

connection.Open();

var cmd = connection.CreateCommand();

cmd.CommandText =
  "create type mood as enum ('happy', 'sad');" +
  "create table people (age integer, cmood mood);";

cmd.ExecuteNonQuery();

connection.ReloadTypes();

var copyCmd =
  "copy people (age, cmood)" +
  "from stdin (format binary)";

using var writer = connection.BeginBinaryImport(copyCmd);

writer.StartRow();
writer.Write(12);
writer.Write(Mood.Happy);
writer.Complete();

enum Mood
{
  Null = 0,
  Sad = 1,
  Happy = 2
}