2
I want to insert a CSV into a postgresql table. CSV contains rows with multiple column numbers.
Ex:
1,2,3
1,2,3,4,5
1,2,3,4,5,6
1,2,3,4
When performing the insertion, the following error occurs:
bind message supplies 7 parameters, but prepared statement "" requires 11.
In this case my CSV has up to 11 columns, but the insertion of the first row only has 7 columns. Can you help me?
My code:
const query = "INSERT INTO category(activitiesheart,activitiessteps,opa,oxe,umt,erf,mar,zap,asd,ser,ver)
VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11)";
pool.connect((err, client, done) => {
if (err) throw err;
try {
csvData.forEach((row) => {
client.query(query, row, (err, res) => {
if (err) {
console.log(err.stack);
} else {
console.log("inserted " + res.rowCount + " row:", row);
}
});
});
} finally {
done();
}
});
Instead of missing values what do you want to do? fill with null?
– Danizavtz
this, is an option too :)
– Matheus Rohde
if possible, the number of VALUE values is dynamic...
– Matheus Rohde
Another thing is how do you know for sure that the element in position "6" belongs to position 6? How do you guarantee that it is not the element in position 11 for example?
– Danizavtz
The code goes through each line of the CSV. In the first line for example, it wants to insert 7 elements, but the Insert is waiting 11 elements. At the moment the CSV has 11 elements, the insertion occurs perfectly. So there’s the problem, the maximum of elements are 11, but there are lines with fewer elements.
– Matheus Rohde
If it contains 7 elements, the rest inserted can be null, 0, anything.
– Matheus Rohde
i understood the problem. I can see by sql the position 6 of the array is
erf
as I know that the value of heading 6 corresponds to the elementerf
and not to the elementver
? There is some order assurance in the array elements?– Danizavtz
The order is 6 = Erf, as you said. There is no guarantee, because if the value does not exist, enter null.
– Matheus Rohde