每日踩坑 2020-11-04 PostgreSQL 批量插入数据

一看居然两三个月没写博文了。凑。最近去考了个试，老天保佑吧。

今天有个同事跑来问我，我之前写的数据同步工具支不支持 PostgreSQL。

然后跟我讲了他的需求。感觉我随手写的小东西开始变成整个部门的标准方案了。。。这让我有点惶恐。君要臣死，臣不得不死啊。

 

那么换驱动，兼容不同的sqlsever，mysql 这些都是老生常谈了。无非就是再加上 PostgreSQL 的驱动而已。

但是所有事情要是那么简单就好了。

首先，我们来百度 PostgreSQL 的批量插入。因为是数据同步，所以对性能还是又一定要求的。

当我们在百度上搜索批量插入 自然应该都会找到这篇。

Npgsql使用入门(三)【批量导入数据】_极客神殿-CSDN博客 

你会发现同样的文章有很多的人转载（抄袭）甚至连个转载声明都没有。唉。很多人创作的心态就是被这些抄袭的人搞炸的。

 

按照文中的代码段思路，是将泛型List 转换为 DataTable 然后直接使用二进制导入的API导入。

此时我按文中代码实现却抛出了异常。我想该不会是因为文章是2016年的太老了吧。

22021: invalid byte sequence for encoding "UTF8": 0x00

这个百度一下看起来是因为字符的编码问题。我花了一上午来尝试从这个角度解决这个问题，全部无果而终。

这里的心酸和弯路就不提了。

 

然后我仔细的看了看我 mysql 源库的数据类型和 pgsql 的类型，发现 pgsql 大部分用的是 varcher。也就是我把大部分 mysql bigint 的值写入了 pgsql 的varchar。

结合我在 stackoverflow上看到的零星解释，我觉得和这个也许有些关系。然后我对照 mysql 调整了 pgsql 的字段类型。

本来我都以为要成功了。然后错误变成了：

 22P03: incorrect binary data format

我心态要崩了。你说这个谁懂啊！！！我在考虑要不要开一下 pgsql 的日志。

然后我看了看 NpgsqlBinaryImporter 其他的 api。发现注释里写的 WriteRow 方法相当于循环调用 Write 方法。（？？？这也太

然后看 Write 方法有些重载：

        public void Write<[NullableAttribute(2)]T([AllowNullAttribute] T value);
        public void Write<[NullableAttribute(2)]T>([AllowNullAttribute] T value, NpgsqlDbType npgsqlDbType);
        public void Write<[NullableAttribute(2)]T>([AllowNullAttribute] T value, string dataTypeName);

NpgsqlDbType 这个参数引起了我的注意。合着 WriteRow 写一组object。那对应的类型它肯定是取不对的 = =。

此时我的心底燃起了希望之火，那么。我们就换这个api来插入数据，代码如下：

                var commandFormat = string.Format(CultureInfo.InvariantCulture, "COPY {0} FROM STDIN (FORMAT BINARY)", TableName);
                using (var writer = (db.Connection as NpgsqlConnection).BeginBinaryImport(commandFormat))
                {
                    foreach (DataRow row in dt.Rows)
                    {
                        writer.StartRow();
                        foreach (DataColumn coll in row.Table.Columns)
                        {
                            var colldbtype = coll.DataType.Name.ToString();
                            NpgsqlTypes.NpgsqlDbType pgtype = NpgsqlTypes.NpgsqlDbType.Bigint;if (colldbtype.ToLower().Contains("int64")) { pgtype = NpgsqlTypes.NpgsqlDbType.Bigint; }
                            if (colldbtype.ToLower().Contains("string")) { pgtype = NpgsqlTypes.NpgsqlDbType.Varchar; }
                            if (colldbtype.ToLower().Contains("datetime")) { pgtype = NpgsqlTypes.NpgsqlDbType.Date; }
                            writer.Write(item[coll.ColumnName], pgtype);
                        }
                    }
                    writer.Complete();

ok，成功。

这种方法的重点是 这里计算的 pgtype 与库中的字段格式必须对应。