Bulk upsert to PostgreSQL table

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for PostgreSQL
Post Reply
[email protected]
Posts: 10
Joined: Wed 29 Jun 2016 13:34

Bulk upsert to PostgreSQL table

Post by [email protected] » Wed 17 Oct 2018 20:20

Following up on this post: viewtopic.php?t=27986.

I need to bulk upsert to a PostgreSQL table that has a SERIAL "id" column. I am trying to use PgSqlDataTable like this:

Code: Select all

PgSqlCommand select = new PgSqlCommand();
select.CommandText = "select * from table ...";
PgSqlDataTable itemsToUpdate = new PgSqlDataTable( select, conn );
itemsToUpdate .FetchAll = true;
itemsToUpdate .Active = true;
itemsToUpdate .RefreshMode = Devart.Common.RefreshRowMode.Both;
itemsToUpdate .RefreshingFields = "id";
itemsToUpdate .Columns[ "id" ].AllowDBNull = true;

foreach( newOrUpdatedItem )
{
   if( item is in itemsToUpdate )
      update the item;
   else
   {
        newItem = itemsToUpdate.NewRow();
        set all values of item except "id";
        itemsToUpdate.Rows.Add( newItem );
   }
}
The problem is that after each newItem is added to the table, I have to call itemsToUpdate.Update() or when I add the next newItem it will throw an exception because the PK is not unique (i.e. the "id" of each new row is null). Calling Update() refreshes the "id" field so the next newItem has a unique "id".

But calling Update() after each added item defeats my whole purpose, which is to do a bulk upsert.

How do I do this? Update the PgSqlDataTable rows for items that already exist and use a Loader for new items?

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

Re: Bulk upsert to PostgreSQL table

Post by Pinturiccio » Mon 22 Oct 2018 09:44

Please describe the issue with more details. We tested it with the following example:

Code: Select all

CREATE TABLE serial_table
(
  id integer NOT NULL DEFAULT nextval('serail_table_id_seq'::regclass),
  val text,
  CONSTRAINT serial_table_pkey PRIMARY KEY (id)
)

INSERT INTO serial_table(val) VALUES ('1');
INSERT INTO serial_table(val) VALUES ('2');
INSERT INTO serial_table(val) VALUES ('3');
And we use the following code:

Code: Select all

PgSqlConnection conn = new PgSqlConnection("your connection string");
conn.Open();
PgSqlDataTable dt = new PgSqlDataTable("select * from serial_table", conn);
dt.FetchAll = true;
dt.RefreshMode = Devart.Common.RefreshRowMode.Both;
dt.Active = true;
dt.Columns["id"].AllowDBNull = true;
dt.Columns["id"].AutoIncrement = false;

var row = dt.NewRow();
row["val"] = 15;
dt.Rows.Add(row);

dt.Rows[0]["val"] = "cc";
dt.Update();
This sample successfully updates an existing row and inserts a new one. How should we modify this example in order to reproduce the issue?

[email protected]
Posts: 10
Joined: Wed 29 Jun 2016 13:34

Re: Bulk upsert to PostgreSQL table

Post by [email protected] » Tue 23 Oct 2018 20:14

The problem happened when I tried to add a second new row. So change it like so:

Code: Select all

var row = dt.NewRow();
row["val"] = 15;
dt.Rows.Add(row);
var row = dt.NewRow();
row["val"] = 16;
dt.Rows.Add(row);
Because "id" is a primary key, it also has a unique constraint. Both of the new rows start out with id = null. When I added the second new row, I got a violation of the unique constraint.

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

Re: Bulk upsert to PostgreSQL table

Post by Pinturiccio » Thu 25 Oct 2018 14:20

Add the following line:

Code: Select all

dt.PrimaryKey = null;
After "dt.Columns["id"].AutoIncrement = false;". Is the issue reproduced after this?

Post Reply