PostgreSQL - How to insert Base64 images strings into a BYTEA column?

Question

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.

How do you want to insert the Base64 image? Via SQL statement? Programmatically? — user871611
– user871611, Commented Mar 14, 2019 at 19:38
Programmatically, using Sequelize's raw queries... I extracted the base64 string from a Buffer using the file system (fs) package in Node.js — user11150028
– user11150028, Commented Mar 14, 2019 at 19:41
@user871611 I added it, of course I did have to add it in the first place, but due to the lack of info I found on how to insert to the bytea I thought that just asking on the SQL query would be enough, thanks for your patience — user11150028
– user11150028, Commented Mar 14, 2019 at 19:53
Shouldn't the decode function (see postgresql.org/docs/9.1/functions-binarystring.html) do the trick? Something like decode(..., 'base64'). — user871611
– user871611, Commented Mar 14, 2019 at 20:06

efirat · Accepted Answer · 2019-11-13 23:45:09Z

9

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

answered Nov 13, 2019 at 23:45

efirat

3,8972 gold badges42 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

David Kamer · Accepted Answer · 2019-03-14 20:41:26Z

1

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

answered Mar 14, 2019 at 20:41

David Kamer

2,9053 gold badges21 silver badges30 bronze badges

Collectives™ on Stack Overflow

PostgreSQL - How to insert Base64 images strings into a BYTEA column?

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related