NpgsqlException did not get the expected error code and how to solve it

less than 1 minute read

When I send a query to PostgreSQL with C # + Npgsql, I want to change the processing according to the type of error that occurred.
The error code could not be obtained correctly.

Below is a list of PostgreSQL 12 error codes
https://www.postgresql.jp/document/12/html/errcodes-appendix.html

This time, I want to SELECT a column that does not exist and get the result with error code 42703 (undefined_column).

Failure example 1


try {
  //SELECT statement(SELECT usr_name FROM users)Send.
  //However, usr_The column named name doesn't exist in the users table, so
  //I want to get the result of 42703.
}
catch(Npgsql.NpgsqlException ex) {
    Console.WriteLine(ex.ErrorCode);    //Result is,-2147467259
}

Failure example 2


try {
  //Abbreviation
}
catch(Npgsql.PostgresException ex) {
    Console.WriteLine(ex.ErrorCode);    //-2147467259
}

Failure example 3


try {
  //Abbreviation
}
catch(System.Data.Common.DbException ex) {
    Console.WriteLine(ex.ErrorCode);    //-2147467259
}

Subtly different


try {
  //Abbreviation
}
catch(Npgsql.PostgresException ex) {
    Console.WriteLine(ex.Message);    //42703:Column"usr_name"Does not exist.
}

success


try {
  //Abbreviation
}
catch(Npgsql.PostgresException ex) {
    Console.WriteLine(ex.SqlState);    //42703
}

So, I caught PostgresException and got the expected error code in SqlState.

Environment C # (.net 5 / C # 8.0) ・ Npgsql 4.1.4 ・ PostgreSQL 12

You May Also Enjoy