PostgreSQL Binary Data Type
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 |
$ 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; |