Insert an image in postgresql database

When the column type is bytea then you could use pg_read_binary_file().

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

See the documentation of pg_read_binary_file.

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)

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.

create table images (imgname text, img bytea);


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

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.

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'));

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'));

We can also use insert into category(category_name,category_image) values('tablette',decode('base 64 image string','base64')); – Navap May 29, 2020 at 2:03

This worked for me. Thanks My observations - the image should be in folder and the folder name should not have hyphen '-'. Also you should not mention extension of the image file like .jpg or .jpeg