npgsql : Selecting a null data throws exception with error "Column is Null"

Question

I am running npgsql v3.7 with .NetCore on Ubuntu.

When I execute a select query and a cell in any row in the results is null, an exception is thrown with the error message "Column is null".

I am having to work around this by putting every column in the select clause inside a case statement which tests for NULL

"CASE WHEN " + fieldName + " IS NULL THEN '' ELSE " + fieldName + " END "

This seems a bit extreme and should not be necessary. Has anyone else come across this.

Thanks.

Answer 1

You are probably trying to read the column like this:

using (var reader = cmd.ExecuteReader()) {
  reader.Next();
  var o = reader.GetString(0);   // Or any other of the Get methods on reader
  ...
}

This code will fail if the column contains a null, and is the expected behavior. In ADO.NET, you need to check for a null value with reader.IsDBNull(0) before actually getting the value. That's just how the database API works.