SQLANDPLSQL.COM

If you want to know more about PostgreSQL data type please refer PostgreSQL Data Types

Bytea

bytea is a binary string data type in postgresql, mainly used with binary data.

bytea is very similar to BLOB (Binary Large Objects) in other databases like Oracle. It can hold maximum 1 GB of data.

Below are commonly used Binary String Functions in PostgreSQL.

Note :- Sometime PgAdmin or IDE’s won’t print correct string, in that case please use postgre CLI or prompt

Operator	Example
Concatenate (||)	select ‘\x123456’::bytea || ‘\x789a00bcde’::bytea; → \x123456789a00bcde
bit_length	select bit_length(‘\x1234’::bytea); → 16
octet_length	select octet_length(‘\x1234’::bytea); → 2
overlay	select overlay(‘\x1234567890’::bytea placing ‘\002\003’::bytea from 2 for 3); → \x12020390
position	select position(‘\x5678’::bytea in ‘\x1234567890’::bytea); → 3
substring	select substring(‘\x1234567890’::bytea from 3 for 2); → \x5678
trim	select trim(‘\x9012’::bytea from ‘\x1234567890’::bytea); → \x345678 select trim(both from ‘\x1234567890’::bytea, ‘\x9012’::bytea); → \x345678
bit_count	select bit_count(‘\x1234567890’::bytea); → 15
btrim	select btrim(‘\x1234567890’::bytea, ‘\x9012’::bytea); → \x345678
get_bit	select get_bit(‘\x1234567890’::bytea, 30); → 1
get_byte	select get_byte(‘\x1234567890’::bytea, 4); → 144
length	select length(‘\x1234567890’::bytea); → 5
ltrim	select ltrim(‘\x1234567890’::bytea, ‘\x9012’::bytea); → \x34567890
md5	select md5(‘Th\000omas’::bytea); → 8ab2d3c9689aaf18b4958c334c82d8b1
rtrim	select rtrim(‘\x1234567890′::bytea,’\x9012’::bytea); → \x12345678
set_bit	select set_bit(‘\x1234567890’::bytea, 30, 0); → \x1234563890
set_byte	select set_byte(‘\x1234567890’::bytea, 4, 64); → \x1234567840
sha224	select sha224(‘abc’::bytea); → \x23097d223405d8228642a477bda255b32aadbce4bda0b3f7e36c9da7
sha256	select sha256(‘abc’::bytea); → \xba7816bf8f01cfea414140de5dae2223b00361a396177a9cb410ff61f20015ad
sha384	select sha384(‘abc’::bytea); → \xcb00753f45a35e8bb5a03d699ac65007272c32ab0eded1631a8b605a43ff5bed8086072ba1e7cc2358baeca134c825a7
sha512	select sha512(‘abc’::bytea); → \xddaf35a193617abacc417349ae20413112e6fa4e89a97ea20a9eeee64b55d39a2192992a274fc1a836ba3c23a3feebbd454d4423643ce80e2a9ac94fa54ca49f
substring	select substr(‘\x1234567890’::bytea, 3, 2); → \x5678
convert	select convert(‘text_in_utf8’, ‘UTF8’, ‘LATIN1’); →\x746578745f696e5f75746638
convert_from	select convert_from(‘text_in_utf8’, ‘UTF8’); → text_in_utf8
convert_to	select convert_to(‘some_text’, ‘UTF8’); → \x736f6d655f74657874
encode	select encode(‘123\000\001’, ‘base64’);→ MTIzAAE=
decode	select decode(‘MTIzAAE=’, ‘base64’);→ 123\000\001

Uses

In general bytea can be used to store large data like image, file etc in byte format. In future release PostgreSQL may release new data type DATALINK , please refer – https://wiki.postgresql.org/wiki/DATALINK