I'm trying to store a Buffer into a bytea type column. I'm using a Postgres database and I have successfully connected to this database with node-postgres. I am able to update any other field, but I just can't find out what the syntax is to properly store a Buffer.
At the moment, there are already images in that database, that were written with a different system and language. I am not able to to re-use this system to achieve what we need.
The output of those existing images is also a Buffer:
<Buffer 89 50 4e 47 0d 0a 1a 0a 00 00 00 0d 49 48 44 52 00 00 04 38 00 00 04 38 08 06 00 00 00 ec 10 6c 8f 00 00 00 04 73 42 49 54 08 08 08 08 7c 08 64 88 00 ... 13315 more bytes>
And I have prepared the an image that should overwrite this value:
<Buffer 75 ab 5a 8a 66 a0 7b fa 67 81 b6 ac 7b ae 22 54 13 91 c3 42 86 82 80 00 00 03 52 52 11 14 80 00 00 2a 00 00 00 2a 02 00 00 00 00 14 48 3e 9a 00 00 00 ... 3153 more bytes>.
All good, so far.
I now need to use the proper SQL UPDATE statement, but I have not been able to figure that out. I have found some answers suggesting converting it using .toString('hex') and prepending it with \\x, but this does not result in the same value format.
My update statement now looks something like this (where imageData is the second Buffer example above):
await pool.query(
`UPDATE image
SET data = '${imageData}'::bytea
WHERE id = '00413567-fdd7-4765-be30-7f80c2d8ce57'`
)
Some requirements:
- I can not use an external file
- I can not use a different value format
- I can not use a different tech stack
