麻烦博主帮忙看一下BulkMerge在PostgreSQL17.10中什么问题，多谢 返回
凌云 发布于1个月前
报错如下： sqlsugarcore 5.1.4.214 PostgreSQL 17.10
2026-06-05 20:16:08,227 [12] WARN ParseRawService - Npgsql.PostgresException (0x80004005): 23505: duplicate key value violates unique constraint "meteralign_jinxin_pkey"
at SqlSugar.PostgreSQLFastBuilder.ExecuteBulkCopyAsync(DataTable dt)
at SqlSugar.FastestProvider`1._BulkCopy(List`1 datas)
at SqlSugar.FastestProvider`1.BulkCopyAsync(List`1 datas)
at SqlSugar.FastBuilder.<>c__DisplayClass27_0`1.<<Merge>b__0>d.MoveNext()
--- End of stack trace from previous location ---
at SqlSugar.ContextMethods.PageEachAsync[T,ResultType](IEnumerable`1 pageItems, Int32 pageSize, Func`2 action)
at SqlSugar.FastBuilder.Merge[T](String tableName, DataTable dt, EntityInfo entityInfo, String[] whereColumns, String[] updateColumns, List`1 datas)
at SqlSugar.FastestProvider`1._BulkMerge(List`1 datas, String[] updateColumns, String[] whereColumns)
at SqlSugar.FastestProvider`1.<>c__DisplayClass33_1.<<BulkMergeAsync>b__0>d.MoveNext()
--- End of stack trace from previous location ---
at SqlSugar.ContextMethods.PageEachAsync[T](IEnumerable`1 pageItems, Int32 pageSize, Func`2 action)
at SqlSugar.FastestProvider`1.BulkMergeAsync(List`1 datas, String[] whereColumns, String[] updateColumns)
at SqlSugar.FastestProvider`1.BulkMerge(List`1 datas)
at xj_DataSort.Service.RawMessageService.ParseThreadEntry() in D:\WorkSpace\xj_datasort\Service\RawMessageService.cs:line 106
Exception data:
Severity: ERROR
SqlState: 23505
MessageText: duplicate key value violates unique constraint "meteralign_jinxin_pkey"
Detail: Detail redacted as it may contain sensitive data. Specify 'Include Error Detail' in the connection string to include this information.
Where: COPY meteralign_jinxin, line 1
SchemaName: public
TableName: meteralign_jinxin
ConstraintName: meteralign_jinxin_pkey
File: nbtinsert.c
Line: 666
Routine: _bt_check_unique
代码如下：
if (!db.DbMaintenance.IsAnyTable("meteralign_jinxin", false))
db.CodeFirst.As<MeterAlign>("meteralign_jinxin").InitTables<MeterAlign>();
List<MeterAlign> alignstests = new List<MeterAlign>();
DateTimeOffset now = new(2026, 6, 5, 1, 0, 0, new(8, 0, 0));
alignstests.Add(new MeterAlign() { MeterId = 110115, VarNo = "00010000", CollectionTime = now, DataSourceId = 0, HourId = now.HourId(), VarVal = "1234", VarNoCollectTime = now.AddMinutes(1) });
var changed = db.Fastest<MeterAlign>().AS("meteralign_jinxin").PageSize(100)
.BulkMerge(alignstests);
实体定义如下
public class MeterAlign
{
[SugarColumn(IsPrimaryKey = true, ColumnName = "hourid")]
public int HourId { get; set; }
[SugarColumn(IsPrimaryKey = true, ColumnName = "meterid")]
public long MeterId { get; set; }
[SugarColumn(IsPrimaryKey = true, ColumnName = "collectiontime")]
public DateTimeOffset CollectionTime { get; set; }
[SugarColumn(IsPrimaryKey = true, ColumnName = "varno")]
public string VarNo { get; set; } = string.Empty;
[SugarColumn(ColumnName = "varval")]
public string VarVal { get; set; } = string.Empty;
[SugarColumn(ColumnName = "varnocollecttime")]
public DateTimeOffset VarNoCollectTime { get; set; }
[SugarColumn(ColumnName = "datasourceid")]
public int DataSourceId { get; set; }
public static string TableName(string tenantname)
=> $"meteralign_{tenantname}";
}
问题出在多次执行，可能数据会有更新，我测试就是拿一条数据反复执行如下语句，就出错了，在SQLSERVER中没问题
var changed = db.Fastest<MeterAlign>().AS("meteralign_jinxin").PageSize(100)
.BulkMerge(alignstests);
请教一下，该怎么解决
热忱回答（4）
-
凌云 VIP0
1个月前
//功能1:存在数据库更新 不存在插入 (默认是主键)Db.Storageable(list2).ExecuteCommand()//（老版本是Saveable）//分页 5.1.4.103-preview04+Db.Storageable(list2).PageSize(1000).ExecuteCommand();Db.Storageable(list2).PageSize(1000,exrows=> { }).ExecuteCommand();看了前面的贴子，发现这个可以替代，暂时能用，就是不知道这个效率和bulkmerge比哪个更好
0 回复 -
凌云 VIP0
1个月前
var list12000 = db.Queryable<MeterAlign>().AS("meteralign_wlmq").Take(12000).ToList();
if (!db.DbMaintenance.IsAnyTable("meteralign_bulkmerge", false))
db.CodeFirst.As<MeterAlign>("meteralign_bulkmerge").InitTables<MeterAlign>();
if (!db.DbMaintenance.IsAnyTable("meteralign_storageable", false))
db.CodeFirst.As<MeterAlign>("meteralign_storageable").InitTables<MeterAlign>();
var sw = Stopwatch.StartNew();
db.Storageable<MeterAlign>(list12000).As("meteralign_storageable").PageSize(10000).ExecuteCommand();
sw.Stop();
mLog.Info($"meteralign_storageable 12000 records insert to db, elapsed={sw.ElapsedMilliseconds}ms");
sw = Stopwatch.StartNew();
db.Fastest<MeterAlign>().AS("meteralign_bulkmerge").PageSize(10000).BulkMerge(list12000);
sw.Stop();
mLog.Info($"meteralign_bulkmerge 12000 records insert to db, elapsed={sw.ElapsedMilliseconds}ms");
list12000.First().VarVal = "999";
sw = Stopwatch.StartNew();
db.Storageable<MeterAlign>(list12000).As("meteralign_storageable").PageSize(10000).ExecuteCommand();
sw.Stop();
mLog.Info($"meteralign_storageable 12000 records update the value of first record to db, elapsed={sw.ElapsedMilliseconds}ms");
DateTimeOffset now = new(2026, 6, 6, 11, 0, 0, new(8, 0, 0));
list12000.Add(new MeterAlign() { MeterId = 110115, VarNo = "00010000", CollectionTime = now, DataSourceId = 0, HourId = now.HourId(), VarVal = "1234", VarNoCollectTime = now.AddMinutes(1) });
list12000.Last().VarVal = "4321";
sw = Stopwatch.StartNew();
db.Storageable<MeterAlign>(list12000).As("meteralign_storageable").PageSize(10000).ExecuteCommand();
sw.Stop();
mLog.Info($"meteralign_storageable 12000 records update the value of last record and add new one record to db, elapsed={sw.ElapsedMilliseconds}ms");
结果
如果改一条数据再用bulkmerge方法，在pgsql下就会有以下问题，sqlserver中没有问题
0 回复 -
凌云 VIP0
1个月前
错误是由BulkCopyAsync()中产生的
具体到如下代码中，用的原生的命令 copy
需要知道哪些是更新的条目，先过滤掉，然后再组COPY "meteralign_bulkmerge" ( "hourid","meterid","collectiontime","varno","varval","varnocollecttime","datasourceid" ) FROM STDIN (FORMAT BINARY)，应该就能通过
0 回复 -
凌云 VIP0
1个月前
通过建立临时表，先把数据bulkMerge进去，再通过sql insert into targettable select from 临时表 on conflict(主键) do update (要更新的字段)
然后再删除临时表，实现曲线救国0 回复