(node:94484) UnhandledPromiseRejectionWarning: error: invalid byte sequence for encoding "UTF8": 0x00
Is this something we can detect/prevent/sanitize on driver level or should I sanitize all strings that touch text type in postgreSQL on application level? (probably using some cumbersome REGEXP). The knex guys suggested that I should contact you.
knex/knex#2284 (comment)
          
A text field in PostgreSQL cannot contain Unicode symbol \u0000. It is reserved by PostgreSQL to represent the end of the string internally.
You can however successfully use \u0001 and so on symbols, i.e. everything else.
And because of that, this library does not need to sanitize knowingly invalid SQL.
          
Thank you for such a quick answer!
What I ended up doing is replacing the byte '\x00' with null. The issue is that I am processing a stream of thousands of messages per minute and even though it's a tiny replacement it still adds up.
Can you expand on what you mean by a stricter validation on the application level?
Cheers!
          
I see - thanks!
I believe I am passing the parameter as a string - like so:
if (singleLoad[3] === '\x00') singleLoad[3] = null await client.query(text, singleLoad)
where text, singleLoad are:
const text =  "INSERT INTO trades(
  eventSymbol,
  eventTime,
  time,
  exchangeCode,
  price,
  change,
) VALUES($1, $2, $3, $4, $5, $6, $7)"
const singleLoad = [
  '.PINS230120C37',
  1600180827366,
  '\x00',
and the table was created like so:
CREATE TABLE trades (
  eventSymbol varchar(50),
  eventTime bigint,
  time bigint,
  exchangeCode bytea,
  price decimal,
  change decimal,
  size bigint
Would passing the parameter as buffer mean something like this?:

singleLoad[3] = new Buffer(singleLoad[3])

I was hoping to not do any computation in the process of inserting this data because its a lot of data coming very fast so it would take a toll - even if small - on the process.
Is there an option or parameter i can alter once so that i dont have to transform every inserted payload?