PostgreSQL Binary Data Type

The binary data type is another useful data type of PostgreSQL to store binary string data. The sequence of bytes or octets is stored in the binary string. The zero-value octet and the non-printable octets can be stored in the field of the binary data type. The raw bytes are stored by the binary strings. The input value of the binary string can be taken by the ‘hex’ or ‘escape’ format and the format of the output depends on the configuration parameter, bytea_output. The default output format is ‘hex’. The BLOB or BINARY LARGE OBJECT is defined by the SQL standard as the binary string type. Different formats and the uses of binary data types in PostgreSQL have been shown in this tutorial.

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:

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 Idéntico Escaped Octets:

Parte Value	Description	Escaped Input	Example	Output
0	Zero octet	E’00′	SELECT E’00′::bytea;	x00
45	Hyphen	‘-‘ or E’55’	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’01′::bytea;	x01

Bytea output Escaped Octets:

Parte Value	Description	Escaped Output	Example	Output
45	Hyphen	–	SELECT E’55′::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’01′::bytea;	01

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 servern«);

$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 recinto 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 servern«);

//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=»https://linuxhint.com/postgresql-binary-data-type/».$filename.«» height=200 width=300 />»;

The generated image from the image data will appear after executing the above script from the recinto 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.

Source link