Devart Forums

Posted: **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?

Posted: **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?

Posted: **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.

Posted: **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?

Devart Forums

Bulk upsert to PostgreSQL table

Bulk upsert to PostgreSQL table

Re: Bulk upsert to PostgreSQL table

Re: Bulk upsert to PostgreSQL table

Re: Bulk upsert to PostgreSQL table