postgres blog - ghdrako/doc_snipets GitHub Wiki

# Create a table to store image data  
CREATE TABLE images (  
image_id serial PRIMARY KEY,  
image_name text,  
image_data bytea  
);

# Import an image file into the "images" table  
INSERT INTO images (image_name, image_data) VALUES ('IMG_1495.png',pg_read_binary_file('/var/lib/pgsql/IMG_1495.png')::bytea);

To retrieve and display an image from a bytea column in PostgreSQL, you can use SQL commands in combination with your application code.

SELECT image_name, image_data FROM images WHERE image_name = 'IMG_1495.PNG';

import psycopg2  
from io import BytesIO  
from PIL import Image  
# Connect to the PostgreSQL database  
conn = psycopg2.connect("dbname=postgres user=postgres password=postgres host=192.168.187.134")  
cur = conn.cursor()  # Retrieve the image data  
cur.execute("SELECT image_data FROM images WHERE image_name = 'IMG_1495.png'")  
image_data = cur.fetchone()[0]  # Close the database connection  
cur.close()  
conn.close()  # Display the image using Python's PIL library (Pillow)  
image = Image.open(BytesIO(image_data))  
image.show()