PostgreSQL BYTEA Data Type

Summary: in this tutorial, you will learn about PostgreSQL BYTEA data type and how to use it to store binary strings in the database.

Introduction to the PostgreSQL BYTEA data type

In PostgreSQL, BYTEA is a binary data type that you can use to store binary strings or byte sequences. BYTEA stands for the binary array.

The following shows how to define a table column with the BYTEA data type:

column_name BYTEACode language: SQL (Structured Query Language) (sql)

The maximum size of a BYTEA column is 1GB. It means you can only store binary data up to 1GB in a single BYTEA column. However, storing a large amount of binary data in a BYTEA column is not efficient.

If files are larger than a few megabytes, you can store them externally and save the paths to the files in the database.

If you work with PHP or Python and want to know how to store binary data in a BYTEA column, you can follow these tutorials:

PostgreSQL BYTEA data type example

First, create a table called binary_data to store binary strings:

CREATE TABLE binary_data(
    id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    data BYTEA
);Code language: SQL (Structured Query Language) (sql)

Second, insert a binary string into the binary_data table:

INSERT INTO binary_data(data) 
VALUES ('\x012345');Code language: SQL (Structured Query Language) (sql)

Third, retrieve data from the BYTEA column:

SELECT * FROM binary_data;Code language: SQL (Structured Query Language) (sql)

Output:

 id |   data
----+----------
  1 | \x012345
(1 row)Code language: SQL (Structured Query Language) (sql)

Summary

Use the BYTEA data type to store small binary data in the database.

Was this tutorial helpful ?