{"id":27520,"date":"2025-04-24T04:00:01","date_gmt":"2025-04-24T04:00:01","guid":{"rendered":"http:\/\/www.withdata.com\/blog\/?p=27520"},"modified":"2026-03-04T05:27:46","modified_gmt":"2026-03-04T05:27:46","slug":"store-images-in-postgresql-bytea","status":"publish","type":"post","link":"https:\/\/www.withdata.com\/blog\/blob\/store-images-in-postgresql-bytea.html","title":{"rendered":"Store images in PostgreSQL BYTEA"},"content":{"rendered":"<h2>PostgreSQL BYTEA Type Features<\/h2>\n<ul>\n<li>BYTEA (byte array) is PostgreSQL&#8217;s native binary data type (replaces legacy BLOB support)<\/li>\n<li>Supports up to 1GB of binary data (limited by PostgreSQL&#8217;s page size and TOAST compression)<\/li>\n<li>Two storage formats: Hex (default in PostgreSQL 9.0+) and Escape (legacy format)<\/li>\n<li>Integrated with TOAST (The Oversized-Attribute Storage Technique) for automatic compression of large BYTEA values<\/li>\n<li>Distinct from pg_largeobject (for very large files &gt;1GB, stored as multiple chunks)<\/li>\n<li>Binary-safe (no character set conversion, ideal for image\/binary file storage)<\/li>\n<\/ul>\n<h2>Step 1: Create a Table with BYTEA Column<\/h2>\n<h3>Basic Table (BYTEA for Standard Image Storage)<\/h3>\n<pre>CREATE TABLE image_storage (\r\n    image_id SERIAL PRIMARY KEY, -- Auto-increment integer primary key\r\n    image_name VARCHAR(100) NOT NULL,\r\n    image_bytea BYTEA NOT NULL, -- BYTEA for image binary data\r\n    upload_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP\r\n);\r\n<\/pre>\n<h3>Optimized Table (With TOAST Compression)<\/h3>\n<pre>-- Explicitly enable TOAST compression (default enabled, but explicit for clarity)\r\nCREATE TABLE image_storage (\r\n    image_id SERIAL PRIMARY KEY,\r\n    image_name VARCHAR(100) NOT NULL,\r\n    image_bytea BYTEA NOT NULL STORAGE EXTERNAL COMPRESS, -- Optimize for large images\r\n    upload_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP\r\n) WITH (autovacuum_enabled = on);\r\n<\/pre>\n<h2>Step 2: Insert Images into PostgreSQL BYTEA<\/h2>\n<h3>Method 1: Using psql\/PGAdmin (Local File)<\/h3>\n<pre>-- Prerequisite: Connect to target database (psql command)\r\n\\c your_database_name;\r\n\r\n-- Method A: Insert using pg_read_binary_file (server-side file)\r\nINSERT INTO image_storage (image_name, image_bytea)\r\nVALUES (\r\n    'product_photo.jpg',\r\n    pg_read_binary_file('\/path\/to\/product_photo.jpg')\r\n);\r\n\r\n-- Method B: Insert using \\lo_import (large object import, convert to BYTEA)\r\n\\lo_import '\/path\/to\/product_photo.jpg' -- Returns a large object OID (e.g., 12345)\r\nINSERT INTO image_storage (image_name, image_bytea)\r\nVALUES ('product_photo.jpg', lo_get(12345));\r\n\\lo_unlink 12345; -- Clean up temporary large object\r\nCOMMIT;\r\n<\/pre>\n<p><strong>Note:<\/strong><\/p>\n<p>1. pg_read_binary_file() requires the file to be on the PostgreSQL server (not client)<\/p>\n<p>2. The PostgreSQL server process must have read permission to the file path<\/p>\n<p>3. For client-side files: Use psql&#8217;s \\copy or PGAdmin&#8217;s import wizard<\/p>\n<h3>Method 2: Using DBBlobEditor<\/h3>\n<h4>Insert Single Image into\u00a0PostgreSQL BYTEA<\/h4>\n<ol>\n<li>Connect to your PostgreSQL database in <a href=\"\/dbblobeditor\/\">DBBlobEditor<\/a><\/li>\n<li>Navigate to the \u201cimage_storage\u201d table in the target schema<\/li>\n<li>Click \u201c+\u201d button to add a record, and then <a href=\"\/blog\/pglobeditor\/view-and-edit-image-stored-in-postgresql-bytea-field.html\">Edit image stored in PostgreSQL BYTEA field<\/a><\/li>\n<\/ol>\n<h4>Batch insert Images into\u00a0PostgreSQL BYTEA<\/h4>\n<ul>\n<li><a href=\"\/blog\/pglobeditor\/import-images-to-postgresql-bytea.html\">Batch import image files into PostgreSQL BYTEA<\/a><\/li>\n<\/ul>\n<h2>Step 3: Retrieve and Restore PostgreSQL BYTEA to Image<\/h2>\n<h3>Method 1: Using PostgreSQL Query + Python Script<\/h3>\n<pre>-- Step 1: Get BYTEA data (PostgreSQL query)\r\nSELECT image_bytea FROM image_storage WHERE image_id = 1;\r\n\r\n-- Step 2: Restore to file (Python script)\r\nimport psycopg2\r\n\r\n# Connect to PostgreSQL\r\nconn = psycopg2.connect(\r\n    host=\"localhost\",\r\n    database=\"your_database_name\",\r\n    user=\"postgres\",\r\n    password=\"your_password\"\r\n)\r\n\r\ncur = conn.cursor()\r\ncur.execute(\"SELECT image_bytea FROM image_storage WHERE image_id = 1\")\r\nbytea_data = cur.fetchone()[0]\r\n\r\n# Write to file\r\nwith open(\"exported_photo.jpg\", \"wb\") as f:\r\n    f.write(bytea_data)\r\n\r\ncur.close()\r\nconn.close()\r\n<\/pre>\n<h3>Method 2: DBBlobEditor Export<\/h3>\n<h4>Retrieve and Restore Single Image<\/h4>\n<ol>\n<li>Connect to PostgreSQL database in DBBlobEditor<\/li>\n<li>Query the \u201cimage_storage\u201d table and locate the target image_bytea column<\/li>\n<li>Double click to <a href=\"\/blog\/pglobeditor\/view-and-edit-image-stored-in-postgresql-bytea-field.html\">View image stored in PostgreSQL BYTEA field<\/a><\/li>\n<li>Click menu \u201cFile\u201d -&gt; \u201cSave\u201d to save BYTEA data as a local image (JPG\/PNG\/GIF\/etc.) file<\/li>\n<\/ol>\n<h4>Batch Export\u00a0PostgreSQL BYTEA to Images<\/h4>\n<ul>\n<li><a href=\"\/blog\/pglobeditor\/export-images-from-postgresql-to-files.html\">Export images from PostgreSQL to files<\/a><\/li>\n<\/ul>\n<h2>PostgreSQL Specific Notes<\/h2>\n<ul>\n<li>For images &gt;1GB: Use <strong>pg_largeobject<\/strong> instead of BYTEA (split large files into chunks)<\/li>\n<li>Adjust <strong>max_wal_size<\/strong> in postgresql.conf for large BYTEA writes (prevents WAL log overflow)<\/li>\n<li>Grant necessary privileges: pg_read_server_files (for pg_read_binary_file), INSERT\/SELECT on tables<\/li>\n<li>Use HEX format (default) over Escape format for better performance with modern PostgreSQL versions<\/li>\n<li>TOAST compression is auto-enabled for BYTEA columns &gt;2KB (reduces storage by 30-70% for images)<\/li>\n<li>Avoid indexing BYTEA columns (use image_id\/image_name for filtering; use pg_trgm for name search)<\/li>\n<\/ul>\n<hr \/>\n<h3>Related Guides<\/h3>\n<ul>\n<li><a href=\"\/blog\/blob\/images-in-blob.html\">Store images in BLOB: A Comprehensive Guide<\/a><\/li>\n<li><a href=\"\/blog\/blob\/store-images-in-db2-blob.html\">Store images in DB2 BLOB<\/a><\/li>\n<li><a href=\"\/blog\/blob\/store-images-in-oracle-blob.html\">Store images in Oracle BLOB<\/a><\/li>\n<li><a href=\"\/blog\/blob\/store-images-in-sql-server-varbinary.html\">Store images in SQL Server VARBINARY<\/a><\/li>\n<li><a href=\"\/blog\/blob\/store-images-in-mysql-blob.html\">Store images in MySQL BLOB<\/a><\/li>\n<li><a href=\"\/blog\/blob\/store-images-in-sqlite-blob.html\">Store images in SQLite BLOB<\/a><\/li>\n<\/ul>\n","protected":false},"excerpt":{"rendered":"<p>PostgreSQL BYTEA Type Features BYTEA (byte array) is PostgreSQL&#8217;s native binary data type (replaces legacy BLOB support) Supports up to 1GB of binary data (limited by PostgreSQL&#8217;s page size and TOAST compression) Two storage formats: Hex (default in PostgreSQL 9.0+) and Escape (legacy format) Integrated with TOAST (The Oversized-Attribute Storage Technique) for automatic compression of [&hellip;]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[4524],"tags":[],"class_list":["post-27520","post","type-post","status-publish","format-standard","hentry","category-blob"],"_links":{"self":[{"href":"https:\/\/www.withdata.com\/blog\/wp-json\/wp\/v2\/posts\/27520","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.withdata.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.withdata.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.withdata.com\/blog\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.withdata.com\/blog\/wp-json\/wp\/v2\/comments?post=27520"}],"version-history":[{"count":7,"href":"https:\/\/www.withdata.com\/blog\/wp-json\/wp\/v2\/posts\/27520\/revisions"}],"predecessor-version":[{"id":45324,"href":"https:\/\/www.withdata.com\/blog\/wp-json\/wp\/v2\/posts\/27520\/revisions\/45324"}],"wp:attachment":[{"href":"https:\/\/www.withdata.com\/blog\/wp-json\/wp\/v2\/media?parent=27520"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.withdata.com\/blog\/wp-json\/wp\/v2\/categories?post=27520"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.withdata.com\/blog\/wp-json\/wp\/v2\/tags?post=27520"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}