Contents
bytea
bytea is a data type which enables the storage of arbitrary raw binary strings regardless of database character encoding, which can contain null bytes and other "unprintable" characters.
bytea has been present in all PostgreSQL versions.
Usage
The bytea type supports two external formats for input and output: the traditional "escape" format, and the newer "hex" format introduced in PostgreSQL 9.0.
The "hex" format is simpler to handle than the "escape" format, but may not be supported by all client applications. In this format, binary data is encoded as 2 hexadecimal digits per byte with the most significant nibble first; the entire string is preceded by the \x sequence. Hexadecimal digits can be upper or lower case; whitespace is ignored.
testdb=> CREATE TABLE bytea_test (data BYTEA);
CREATE TABLE
testdb=> INSERT INTO bytea_test VALUES(E'\\xDEADBEEF 005c 313233');
INSERT 0 1
testdb=> SELECT * FROM bytea_test ;
data
---------------------------
\xdeadbeef005c313233
(1 row)
The traditional "escape" format represents binary data as a sequence of ASCII characters, with "unprintable" characters being escaped as their three-digit octal values. This format is somewhat cumbersome; the value used in the example above would need to be entered as:
testdb=> INSERT INTO bytea_test VALUES('\336\255\276\357\000\\123');
bytea data can be entered in either format; output defaults to "hex" and is controlled by the GUC option bytea_output:
testdb=> SET bytea_output TO 'escape';
SET
testdb=> SELECT * FROM bytea_test ;
data
---------------------------
\336\255\276\357\000\\123
(1 row)
Change history
- PostgreSQL 9.0
- hex-string input and output added (commit a2a8c7a6)
References
- PostgresSQL documentation: Binary Data Types
