PostgreSQL - How to insert Base64 images strings into a BYTEA column?
Solution 1
Try this:
insert into table_name (image)
values decode('AcAAFBAO5Az....AQAAAFBCO5gT/AEAABT', 'base64')
Here is some information about decode
https://www.base64decode.net/postgresql-decode
Solution 2
To answer the question regarding Postgres and Sequelize:
You will need to use the Sequelize.BLOB('tiny')
datatype to model a BYTEA
Postgres datatype.
Here is more information about datatypes in Sequelize (it also contains the above information):
http://docs.sequelizejs.com/manual/data-types.html
When converting into a tiny blob, Postgres will default to utf-8, meaning you'll probably want to turn your data into a utf-8 encoding, store the data, and the read it as utf-8.
EDIT:
You will use Base64 to encode the image binary data into an ASCII string: https://developer.mozilla.org/en-US/docs/Web/API/WindowBase64/Base64_encoding_and_decoding
![Admin](/assets/logo_square_200-5d0d61d6853298bd2a4fe063103715b4daf2819fc21225efa21dfb93e61952ea.png)
Admin
Updated on June 17, 2022Comments
-
Admin about 2 years
I have the following SQL:
CREATE TABLE Documents ( Id INT NOT NULL, UserId INT NOT NULL, Label CHARACTER VARYING(220) NOT NULL, Image BYTEA NOT NULL, PRIMARY Key(Id), FOREIGN KEY (UserId) REFERENCES Users(Id) );
I want to know, How should I have to insert the Base64 image into the table.
The Base64 string comes from a Buffer from after getting the image using the fs module on Node.js.
I'm attempting to insert the image using raw queries of Sequelize, but I have not found proper information on this.