Is IsNullable always trune and IsAutoincrement always false for Npgsql?

Question

I have a program written in C# using VS2012 that automatically builds wrapper classes for database tables. I am trying to update it to make sure that null values are handled intelligently (a new concept for my company). Most of our work is done using PostgreSQL, and we usually use ODBC. I want my program to be able to recognize nullable and autoincrement columns. The DataColumn class includes IsNullable and IsAutoincrement properties. I created a little table with samples of each type of column. Using ODBC, all columns were found to be nullable and not autoincremented. I thought that was because ODBC doesn't implement everything, so I tried it with the latest version of Npgsql. I was surprised to see that Npgsql also reported everything nullable and not autoincrementing. Is there something I need to do to have those properties be set?

Here's my table definition:

CREATE TABLE nullable_test
(
  key_field bigserial NOT NULL,
  non_nullable_integer integer NOT NULL,
  nullable_integer integer
)
WITH (
  OIDS=FALSE
);

And here's my test program:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.Odbc;

using Npgsql;

namespace NullableTest
{
    class Program
    {
        static void Main(string[] args)
        {
            try
            {
                NpgsqlConnection npgConn = new NpgsqlConnection();
                NpgsqlConnectionStringBuilder connStringBuilder = new NpgsqlConnectionStringBuilder();
                connStringBuilder.Host = "localhost";
                connStringBuilder.Database = "Stripco";
                connStringBuilder.Username = "caps";
                connStringBuilder.Password = "asdlkjqp";
                npgConn.ConnectionString = connStringBuilder.ToString();
                npgConn.Open();
                Console.WriteLine("Database open using Npgsql");

                DataSet npgsqlDataSet = new DataSet();
                NpgsqlDataAdapter npgsqlAdapter = new NpgsqlDataAdapter("select * from nullable_test", npgConn);
                npgsqlAdapter.Fill(npgsqlDataSet);
                Console.WriteLine("Data set is filled.");

                DataTable table = npgsqlDataSet.Tables[0];
                DataColumn keyColumn = npgsqlDataSet.Tables[0].Columns["key_field"];
                DataColumn nonNullableColumn = npgsqlDataSet.Tables[0].Columns["non_nullable_integer"];
                DataColumn nullableColumn = npgsqlDataSet.Tables[0].Columns["nullable_integer"];

                Console.WriteLine("Key column is " + (keyColumn.AutoIncrement ? "" : " not ") + " autoincrementing");
                Console.WriteLine("Key column is " + (keyColumn.AllowDBNull ? "" : " not ") + " allowing nulls.");
                Console.WriteLine("Non-nullable column is " + (nonNullableColumn.AutoIncrement ? "" : " not ") + " autoincrementing");
                Console.WriteLine("Non-nullable column is " + (nonNullableColumn.AllowDBNull ? "" : " not ") + " allowing nulls.");
                Console.WriteLine("Nullable column is " + (nullableColumn.AutoIncrement ? "" : " not ") + " autoincrementing");
                Console.WriteLine("Nullable column is " + (nullableColumn.AllowDBNull ? "" : " not ") + " allowing nulls.");

                npgConn.Close();

            }
            catch (Exception ex)
            {
                Console.WriteLine("Failed to open database: " + ex.Message);
            }
            Console.WriteLine("Press any key");
            Console.ReadKey();
        }
    }
}

Answer 1

You have to understand the difference between getting metadata information on a resultset (what you seem to be doing) vs. getting info on a table column - the two aren't the same.

When you get metadata for a table (via NpgsqlConnection.GetSchemaTable(), Npgsql goes and finds all the information it can, including null ability and auto-increment. However, when getting information about a resultset, Npgsql has almost no info provided by PostgreSQL and cannot know whether it's nullable or autoincrement.

So to get all the info, use NpgsqlConnection.GetSchemaTable().