Withdata Software

Store images in PostgreSQL BYTEA

Shiji Pan

PostgreSQL BYTEA Type Features

Step 1: Create a Table with BYTEA Column

Basic Table (BYTEA for Standard Image Storage)

CREATE TABLE image_storage (
    image_id SERIAL PRIMARY KEY, -- Auto-increment integer primary key
    image_name VARCHAR(100) NOT NULL,
    image_bytea BYTEA NOT NULL, -- BYTEA for image binary data
    upload_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Optimized Table (With TOAST Compression)

-- Explicitly enable TOAST compression (default enabled, but explicit for clarity)
CREATE TABLE image_storage (
    image_id SERIAL PRIMARY KEY,
    image_name VARCHAR(100) NOT NULL,
    image_bytea BYTEA NOT NULL STORAGE EXTERNAL COMPRESS, -- Optimize for large images
    upload_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) WITH (autovacuum_enabled = on);

Step 2: Insert Images into PostgreSQL BYTEA

Method 1: Using psql/PGAdmin (Local File)

-- Prerequisite: Connect to target database (psql command)
\c your_database_name;

-- Method A: Insert using pg_read_binary_file (server-side file)
INSERT INTO image_storage (image_name, image_bytea)
VALUES (
    'product_photo.jpg',
    pg_read_binary_file('/path/to/product_photo.jpg')
);

-- Method B: Insert using \lo_import (large object import, convert to BYTEA)
\lo_import '/path/to/product_photo.jpg' -- Returns a large object OID (e.g., 12345)
INSERT INTO image_storage (image_name, image_bytea)
VALUES ('product_photo.jpg', lo_get(12345));
\lo_unlink 12345; -- Clean up temporary large object
COMMIT;

Note:

1. pg_read_binary_file() requires the file to be on the PostgreSQL server (not client)

2. The PostgreSQL server process must have read permission to the file path

3. For client-side files: Use psql’s \copy or PGAdmin’s import wizard

Method 2: Using DBBlobEditor

Insert Single Image into PostgreSQL BYTEA

  1. Connect to your PostgreSQL database in DBBlobEditor
  2. Navigate to the “image_storage” table in the target schema
  3. Click “+” button to add a record, and then Edit image stored in PostgreSQL BYTEA field

Batch insert Images into PostgreSQL BYTEA

Step 3: Retrieve and Restore PostgreSQL BYTEA to Image

Method 1: Using PostgreSQL Query + Python Script

-- Step 1: Get BYTEA data (PostgreSQL query)
SELECT image_bytea FROM image_storage WHERE image_id = 1;

-- Step 2: Restore to file (Python script)
import psycopg2

# Connect to PostgreSQL
conn = psycopg2.connect(
    host="localhost",
    database="your_database_name",
    user="postgres",
    password="your_password"
)

cur = conn.cursor()
cur.execute("SELECT image_bytea FROM image_storage WHERE image_id = 1")
bytea_data = cur.fetchone()[0]

# Write to file
with open("exported_photo.jpg", "wb") as f:
    f.write(bytea_data)

cur.close()
conn.close()

Method 2: DBBlobEditor Export

Retrieve and Restore Single Image

  1. Connect to PostgreSQL database in DBBlobEditor
  2. Query the “image_storage” table and locate the target image_bytea column
  3. Double click to View image stored in PostgreSQL BYTEA field
  4. Click menu “File” -> “Save” to save BYTEA data as a local image (JPG/PNG/GIF/etc.) file

Batch Export PostgreSQL BYTEA to Images

PostgreSQL Specific Notes

Related Guides