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.
bytea type supports two external formats for input and output: the traditional "
escape" format, and the newer "
hex" format introduced in PostgreSQL 9.0.
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');
testdb=> SET bytea_output TO 'escape'; SET testdb=> SELECT * FROM bytea_test ; data --------------------------- \336\255\276\357\000\\123 (1 row)