PostgreSQL BYTEA Data Type Explained with Examples
The BYTEA data type in PostgreSQL is used to store binary data like images, files, and other multimedia content. Learn how to use it with examples.
What is the BYTEA Data Type in PostgreSQL?
The BYTEA (Binary Data) data type in PostgreSQL is designed to store raw binary data or byte arrays. It is commonly used to handle non-textual data like images, documents, or audio files.
Key Features of BYTEA:
Syntax of BYTEA
CREATE TABLE table_name (
column_name BYTEA
);
Example Usage of BYTEA in PostgreSQL
1. Creating a Table with BYTEA Column
Code:
-- Create a table to store files
CREATE TABLE file_storage (
id SERIAL PRIMARY KEY, -- Auto-incrementing ID
file_name TEXT, -- Name of the file
file_data BYTEA -- Binary data for the file
);
2. Inserting Binary Data into BYTEA
Code:
-- Insert binary data into the table
INSERT INTO file_storage (file_name, file_data)
VALUES ('example.txt', decode('48656c6c6f20576f726c64', 'hex'));
decode('48656c6c6f20576f726c64', 'hex'): Converts hexadecimal representation of "Hello World" into binary.
3. Retrieving and Displaying Data
Code:
-- Retrieve binary data
SELECT file_name, encode(file_data, 'hex') AS file_content
FROM file_storage;
encode(file_data, 'hex'): Converts binary data back into a readable hex string.
4. Updating Binary Data
Code:
-- Update binary data in the table
UPDATE file_storage
SET file_data = decode('4e65772044617461', 'hex') -- New data: "New Data"
WHERE id = 1;
5. Deleting a Record
Code:
-- Delete a file entry
DELETE FROM file_storage
WHERE id = 1;
Explanation of BYTEA Functions:
Practical Use Cases
Performance Considerations:
All PostgreSQL Questions, Answers, and Code Snippets Collection.
RetroSearch is an open source project built by @garambo | Open a GitHub Issue
Search and Browse the WWW like it's 1997 | Search results from DuckDuckGo
HTML:
3.2
| Encoding:
UTF-8
| Version:
0.7.4