nodeJS将数据插入PostgreSQL错误 [英] nodeJS inserting Data into PostgreSQL error

问题描述

使用NodeJS和PostgreSQL时出现一个奇怪的错误，希望您能为我提供帮助。

I have a weird error using NodeJS with a PostgreSQL and I hope you can maybe help me out.

我有大量的数据集，大约有200万个我想插入到数据库中的条目。

I have a huge amount of data sets, about 2 Million entries that I want to insert into my DB.

一个数据包含4列：

id: string,
points: float[][]
mid: float[]
occurences: json[]

我正在像这样插入数据：

I am inserting data like so:

let pgp = require('pg-promise')(options);
let connectionString = 'postgres://archiv:archiv@localhost:5432/fotoarchivDB';
let db = pgp(connectionString);

cityNet.forEach((arr) => {
    db
    .none(
        "INSERT INTO currentcitynet(id,points,mid,occurences) VALUES $1",
        Inserts("${id},${points}::double precision[],${mid}::double precision[],${occurences}::json[]",arr))
    .then(data => {
        //success
    })
    .catch(error => {
        console.log(error);
        //error
    });
})

function Inserts(template, data) {
    if (!(this instanceof Inserts)) {
        return new Inserts(template, data);
    }
    this._rawDBType = true;
    this.formatDBType = function() {
    return data.map(d => "(" + pgp.as.format(template, d) + ")").join(",");
};

这完全适用于前309248个数据，然后突然出现以下错误for（它看起来像）它尝试插入的每个下一个数据：

This works out for exactly for the first 309248 data pieces, then suddenly it just errors out with the following for (what it seems like) every next data it tries to insert:

{ error: syntax error at end of input
at Connection.parseE (/home/christian/Masterarbeit_reworked/projekt/server/node_modules/pg-promise/node_modules/pg/lib/connection.js:539:11)
at Connection.parseMessage (/home/christian/Masterarbeit_reworked/projekt/server/node_modules/pg-promise/node_modules/pg/lib/connection.js:366:17)
at Socket.<anonymous> (/home/christian/Masterarbeit_reworked/projekt/server/node_modules/pg-promise/node_modules/pg/lib/connection.js:105:22)
at emitOne (events.js:96:13)
at Socket.emit (events.js:188:7)
at readableAddChunk (_stream_readable.js:176:18)
at Socket.Readable.push (_stream_readable.js:134:10)
at TCP.onread (net.js:548:20)
name: 'error',
length: 88,
severity: 'ERROR',
code: '42601',
detail: undefined,
hint: undefined,
position: '326824',
internalPosition: undefined,
internalQuery: undefined,
where: undefined,
schema: undefined,
table: undefined,
column: undefined,
dataType: undefined,
constraint: undefined,
file: 'scan.l',
line: '1074',
routine: 'scanner_yyerror' }

位置项会随着每次迭代的错误消息而更改。

The 'position' entry changes for every iterating error-message.

我可以重做一次，并且在309248个条目之后它总是会出错。
当我尝试插入较少的内容（例如1000个条目）时，不会发生此错误。

I can redo that and it will always error after 309248 entries. When I try to insert less, like 1000 entries, the error does not occur.

那确实让我感到困惑。我以为PostgreSQL没有最大行数。错误消息也根本无法帮助我。

That really confuses me. I thought PostgreSQL does not have any max amount of rows. Also the error message does not help me at all.

已解决
发现了错误。在我的数据中，有空条目。筛选出无效数据。
我将尝试其他插入数据的建议，因为当前的方式可行，但是性能非常糟糕。

SOLVED The error was found. In my data there were "null" entries that have slipped into it. Filtering out null-data worked out. I will try out the other recommendations for inserting data, since the current way works, but the performance is very crappy.

推荐答案

我不确定，但是看起来您在最后一个元素（309249）上的数据结构错误，并且PostgreSQL无法解析某些属性

I'm not sure, but it looks like you got wrong data structure at the last element(309249) and PostgreSQL cannot parse some property

这篇关于nodeJS将数据插入PostgreSQL错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！