PgSqlLoader primary key column

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for PostgreSQL
Post Reply
che1972
Posts: 8
Joined: Thu 04 Sep 2014 12:02

PgSqlLoader primary key column

Post by che1972 » Thu 04 Sep 2014 12:22

A few weeks ago I updated my driver from 5.30.172 to the current one. Ok, after 3 years without updating I expected that there will be problems. A few of them I could resolve by reading the changelog.
But now I have a problem with PgSqlLoader. I use SetValue(), but I can't set the primary key id (integer type). I found out that the primary key column (serial type) is not in the list of columns in the PgSqlLoader object. Is this a bug or a feature? And is there a workaround? I don't want to let the database set the primary key in this case.
Thank you in advance.

Pinturiccio
Devart Team
Posts: 2125
Joined: Wed 02 Nov 2011 09:44

Re: PgSqlLoader primary key column

Post by Pinturiccio » Mon 08 Sep 2014 15:27

The reason of this behavior is that the Serial type is an autoincrement type, and value is not required for a column of such type when inserting records. If such a column is created in the Columns collection, it would require setting values for it, because passing NULL value will cause an error. That's why columns of the type Serial are not added to the Columns collection when calling the CreateColumns method of the PgSqlLoader class.

If you want to specify values for a Serial column manually, you need to add this column to the Columns collection manually:

Code: Select all

PgSqlConnection conn = new PgSqlConnection("connection string");
conn.Open();

PgSqlLoader loader = new PgSqlLoader();
loader.Connection = conn;
loader.TableName = "loader";
loader.CreateColumns();
loader.Columns.Add("column name", PgSqlType.Int, 10, 10, 0);
            
loader.Open();

che1972
Posts: 8
Joined: Thu 04 Sep 2014 12:02

Re: PgSqlLoader primary key column

Post by che1972 » Tue 09 Sep 2014 11:50

I already thought that the reason of this behaviour is the serial type of the column.
The workaround is fine, thank you!

Post Reply