nodejs insert postgresql tables

Question

I am trying insert random data(size must be 250K) to the table. here is my table schema DDL: CREATE TABLE entries (entryID serial PRIMARY KEY NOT NULL,content bytea);

Here is my code:

'use strict';

var pg  = require('pg');
var randomBytes = require('randombytes');

var pool = new pg.Pool({
 host     : 'xxx',
 user     : 'postgres',
 password : 'xxxx',
 database : 'mydb',
 max:20,
 idleTimeoutMillis: 3000,
});

async function run(){

 for (let i=0; i<100000; i++){

 await new Promise(done => setTimeout(done, 100));
 await pool.connect(function (error, connection) {
   if (error) throw error;
   connection.query('insert into entries(content) values($1)', [randomBytes(8)+Buffer.alloc(249992,1)], function (error, results, fields) {
     if (error) throw error;
     connection.release();
     console.log("Current row "+ i + "!");
   });
 });
}
}
run();

everytime i run the code , after server rounds, it errored as infor : error: invalid byte sequence for encoding "UTF8": 0x00 or error: invalid input syntax for type bytea

here is the env info: psql (13.11 (Debian 13.11-0+deb11u1), server 14.7) SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)

i run the similar code in mysql(with mediumblob ) is fine. how can i run it in postgresql

Does this answer your question? [Postgres error on insert - ERROR: invalid byte sequence for encoding "UTF8": 0x00](https://stackoverflow.com/questions/1347646/postgres-error-on-insert-error-invalid-byte-sequence-for-encoding-utf8-0x0) — Vasyl Moskalov, Aug 03 '23 at 03:34
no, i already use bytea field for the data, and i insert the data with js buffer with "1" be filled. — Shichao Dong, Aug 03 '23 at 04:49

score 0 · Accepted Answer · answered Aug 03 '23 at 05:20

Use this code, This code properly constructs the content buffer using Buffer.concat() and handles the asynchronous connection properly using async/await inside the pool.connect() callback;

'use strict';

var pg = require('pg');
var randomBytes = require('randombytes');

var pool = new pg.Pool({
    host: 'xxx',
    user: 'postgres',
    password: 'xxxx',
    database: 'mydb',
    max: 20,
    idleTimeoutMillis: 3000,
});

async function run() {
    try {
        for (let i = 0; i < 100000; i++) {
            await new Promise(done => setTimeout(done, 100));

            const content = Buffer.concat([randomBytes(8), Buffer.alloc(249992, 1)]);

            await pool.connect(async (error, connection) => {
                if (error) throw error;

                try {
                    await connection.query('insert into entries(content) values($1)', [content]);
                    console.log("Current row " + i + "!");
                } finally {
                    connection.release();
                }
            });
        }
    } catch (error) {
        console.error("Error:", error);
    } finally {
        pool.end();
    }
}

run();

Remember to replace 'xxx', 'postgres', 'xxxx', 'mydb', and any other connection details with the appropriate values for your PostgreSQL database configuration.

Thanks Abid, It works fine. i don't know why, but it seems my code sometimes doesn't constructs the data i want. — Shichao Dong, Aug 04 '23 at 02:08

nodejs insert postgresql tables

1 Answers1