Pre-requisites:
You have to install the latest version of PostgreSQL packages on the Linux operating system before executing the SQL statements shown in this tutorial. Run the following commands to install and start the PostgreSQL:
1 2 3 | $ sudo apt-get -y install postgresql postgresql-contrib $ sudo systemctl start postgresql.service |
Run the following command to login to PostgreSQL with root permission:
1 | $ sudo -u postgres psql |
Bytea Hex Format:
The binary data is encoded as two hexadecimal digits per byte in hex format. The binary string is preceded by the sequence, \x. The hexadecimal digits can be either uppercase or lower case. This format is supported by a wide range of external applications.
Example:
1 | # SELECT E'\\xABC0110' AS hex_format; |
Bytea Escape Format:
The escape format is the traditional PostgreSQL format. A sequence of ASCII characters is used to represent the binary data in escape format. The binary string is converted into a three-digit octal value preceded by two backslashes.
Bytea Literal Escaped Octets:
| Decimal Value | Description | Escaped Input | Example | Output |
|---|---|---|---|---|
| 0 | Zero octet | E’\\000′ | SELECT E’\\000′::bytea; | \x00 |
| 45 | Hyphen | ‘-‘ or E’\\055’ | SELECT E’\-‘::bytea; | \x2d |
| 110 | ‘n’ | ‘n’ or E’\\156′ | SELECT E’\n’::bytea; | \x6e |
| 0 to 31 and 127 to 255 | Non-printable octets | E’\\xxx'(octal value) | SELECT E’\\001′::bytea; | \x01 |
Bytea output Escaped Octets:
| Decimal Value | Description | Escaped Output | Example | Output |
|---|---|---|---|---|
| 45 | Hyphen | – | SELECT E’\\055′::bytea; | – |
| 32 to 126 | Printable octets | Any printable character | SELECT E’\\156′::bytea; | n |
| 0 to 31 and 127 to 255 | Non-printable octets | \xxx(octal value) | SELECT E’\\001′::bytea; | \001 |
Use of Binary data type in PostgreSQL:
Before creating any table with the Boolean data type, you have to create a PostgreSQL database. So, run the following command to create a database named ‘testdb’:
1 | # CREATE DATABASE testdb; |
The following output will appear after creating the database:
Example-1: Create a table with a binary data type to store octal value
Create a table named ‘tbl_binary_1’ in the current database with two fields. The first field name is id which is the primary key of the table. The value of this field will be incremented automatically when a new record will insert. The second field name is binary_data and the data type is BYTEA.
1 2 3 4 | # CREATE TABLE tbl_binary_1 ( Id SERIAL PRIMARY KEY, binary_data BYTEA); |
The following output will appear after executing the above query:
Run the following INSERT query that will insert two octal values into the tbl_binary_1 table:
1 2 3 4 5 | # INSERT INTO tbl_binary_1 (binary_data) VALUES (E'\\055'), (E'\\156'); |
The following output will appear after executing the above query:
Run the following SELECT query that will read all records from the tbl_binary_1 table:
1 | # SELECT * FROM tbl_binary_1; |
The following output will appear after executing the above query. The output shows the hexadecimal value of the octal value.
Example-2: Create a table with a binary data type to store image data
Create a table named ‘tbl_binary_2’ in the current database with three fields. The first field name is id which is the primary key of the table and the value of this field will be incremented automatically when a new record will be inserted. The second field name is image_name and the data type is VARCHAR (20). The image name will be stored in this field. The third field name is image_data and the data type of this field is BYTEA. The image data will be stored in this field.
1 2 3 4 5 | # CREATE TABLE tbl_binary_2 ( Id SERIAL PRIMARY KEY, image_name VARCHAR(20), image_data BYTEA); |
The following output will appear after executing the above query.
Insert an image in the table using PHP:
Create a PHP file named insert_image.php with the following code that will read the content of an image file. Then, store the image in the PostgreSQL table after converting it into binary data.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 | <?php //Display error setting ini_set('display_errors', 1); error_reporting(E_ALL); $host = "localhost"; $user = "postgres"; $pass = "12345"; $db = "testdb"; //Create database connection object $db_connection = pg_connect("host=$host dbname=$db user=$user password=$pass") or die ("Could not connect to server\n"); $filename = "flower.png"; $image = fopen($filename, 'r') or die("Unable to open the file."); $data = fread($image, filesize($filename)); $cdata = pg_escape_bytea($data); fclose($image); //Insert the image data $query = "INSERT INTO tbl_binary_2(image_name, image_data) Values('$filename', '$cdata')"; $result = pg_query($db_connection, $query); if($result) echo "Image data is inserted successfully."; pg_close($db_connection); ?> |
The following output will appear after executing the above script from the local server and the image file existing in the current location:
Read the image data from the table using PHP:
Create a PHP file named get_image.php with the following code that will read the binary data of an image file. Create the image from the binary data and display the image in the browser.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 | <?php //Display error setting ini_set('display_errors', 1); error_reporting(E_ALL); $host = "localhost"; $user = "postgres"; $pass = "12345"; $db = "testdb"; //Create database connection object $db_connection = pg_connect("host=$host dbname=$db user=$user password=$pass") or die ("Could not connect to server\n"); //Read the image data from the table $query = "SELECT image_data FROM tbl_binary_2 WHERE id=1"; $result = pg_query($db_connection, $query) or die (pg_last_error($db_connection)); $data = pg_fetch_result($result, 'image_data'); $cimage = pg_unescape_bytea($data); //Create an image file with the image data retrieved from the table $filename = "myfile.jpg"; $image = fopen($filename, 'wb') or die("Unable to open image."); fwrite($image, $cimage) or die("Unable to write data."); fclose($image); pg_close($db_connection); //Display the image in the browser echo "<img src='".$filename."' height=200 width=300 />"; ?> |
The generated image from the image data will appear after executing the above script from the local server.
Conclusion:
The purpose of using binary data types and different uses of binary data in PostgreSQL has been shown in this tutorial that will help the new PostgreSQL user to work with the binary data type.
