Insert an image in postgresql database

Question

I would like to know How can I insert an image "bytea" into a table of my postgreSql database? I've been searching forums for hours and have seen the same question posted dozens of times, but yet to find a single answer. All I see is how to insert .jpeg's into an old column which isn't what I need.

Here's the database table:

create table category  (
"id_category" SERIAL,
"category_name" TEXT,
"category_image" bytea,
constraint id_cat_pkey primary key ("id_category"))without oids;

and when I add a new line, it doesn't work :

insert into category(category_name,category_image) values('tablette', lo_import('D:\image.jpg'));

Answer 1

If the column type is bytea then you can simply use the 'pg_read_binary_file'.

Example: pg_read_binary_file('/path-to-image/')

check postgresql documentation of pg_read_binary_file

Answer 2

None of the above example worked well for me and on top of that I needed to add many images at once.

Full working example (python 3) with explanations:

With get_binary_array we get the value of the image (or file) as a binary array, using its path and file name as parameter (ex: '/home/Pictures/blue.png').

With send_files_to_postgresql we send all the images at once.

I previously created the database with one sequential 'id' that will automatically be incremented (but you can use your own homemade id) and one bytea 'image' field

import psycopg2

def get_binary_array(path):
    with open(path, "rb") as image:
        f = image.read()
        b = bytes(f).hex()
        return b

def send_files_to_postgresql(connection, cursor, file_names):
    query = "INSERT INTO table(image) VALUES (decode(%s, 'hex'))"
    mylist = []
    for file_name in file_names:
        mylist.append(get_binary_array(file_name))

    try:
        cursor.executemany(query, mylist)
       
        connection.commit()  # commit the changes to the database is advised for big files, see documentation
        count = cursor.rowcount # check that the images were all successfully added
        print (count, "Records inserted successfully into table")
    except (Exception, psycopg2.DatabaseError) as error:
        print(error)

def get_connection_cursor_tuple():
    connection = None
    try:
        params = config()
        print('Connecting to the PostgreSQL database...')
        connection = psycopg2.connect(**params)
        cursor = connection.cursor()
    except (Exception, psycopg2.DatabaseError) as error:
        print(error)

    return connection, cursor

connection, cursor = connect_db.get_connection_cursor_tuple()
img_names = ['./blue.png', './landscape.jpg']
send_files_to_postgresql(connection, cursor, img_names)

Answer 3

insert into category(category_name,category_image) values('tablette', bytea('D:\image.jpg'));

The above solution works if column type is bytea

insert into category(category_name,category_image) values('tablette', lo_import('D:\image.jpg'));

The above solution works if column type is oid i.e., Blob

insert into category(category_name,category_image) values('tablette',decode('HexStringOfImage',hex));

The above decode function take two parameters. First parameter is HexString of Image.The second parameter is hex by default.Decode function coverts the hexString to bytes and store in bytea datatype column in postgres.

Answer 4

Something like this function (slightly adapted from here) could work out.

create or replace function img_import(filename text)
  returns void
  volatile
  as $$
    declare
        content_ bytea;
        loid oid;
        lfd integer;
        lsize integer;
    begin
        loid := lo_import(filename);
        lfd := lo_open(loid,131072);
        lsize := lo_lseek(lfd,0,2);
        perform lo_lseek(lfd,0,0);
        content_ := loread(lfd,lsize);
        perform lo_close(lfd);
        perform lo_unlink(loid);

    insert into category values
    ('tablette',
    content_);
    end;
$$ language plpgsql

Use it like select * from img_import('D:\image.jpg'); or rewrite to procedure if feeling like it.

Answer 5

create below function:

create or replace function bytea_import(p_path text, p_result out bytea) 
                       language plpgsql as $$
    declare
      l_oid oid;
    begin
      select lo_import(p_path) into l_oid;
      select lo_get(l_oid) INTO p_result;
      perform lo_unlink(l_oid);
    end;$$;

and use like this:

insert into table values(bytea_import('C:\1.png'));

Answer 6

For Linux users this is how to add the path to the image

insert into blog(img) values(bytea('/home/samkb420/Pictures/Sam Pics/sam.png'));

Answer 7

create table images (imgname text, img bytea);


insert into images(imgname,img) values ('MANGO', pg_read_binary_file('path_of_image')::bytea);

Answer 8

Use SQL workbench - Database explorer - insert a row and follow the dialogue...

enter image description here