PostgreSQL bytea or BLOB data type and related Functions and Operators

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

OperatorExample
Concatenate (||)select ‘\x123456’::bytea || ‘\x789a00bcde’::bytea; → \x123456789a00bcde
bit_lengthselect bit_length(‘\x1234’::bytea); → 16
octet_lengthselect octet_length(‘\x1234’::bytea); → 2
overlayselect overlay(‘\x1234567890’::bytea placing ‘\002\003’::bytea from 2 for 3); → \x12020390
positionselect position(‘\x5678’::bytea in ‘\x1234567890’::bytea); → 3
substringselect substring(‘\x1234567890’::bytea from 3 for 2); → \x5678
trimselect trim(‘\x9012’::bytea from ‘\x1234567890’::bytea); → \x345678
select trim(both from ‘\x1234567890’::bytea, ‘\x9012’::bytea); → \x345678
bit_countselect bit_count(‘\x1234567890’::bytea); → 15
btrimselect btrim(‘\x1234567890’::bytea, ‘\x9012’::bytea); → \x345678
get_bitselect get_bit(‘\x1234567890’::bytea, 30); → 1
get_byteselect get_byte(‘\x1234567890’::bytea, 4); → 144
lengthselect length(‘\x1234567890’::bytea); → 5
ltrimselect ltrim(‘\x1234567890’::bytea, ‘\x9012’::bytea); → \x34567890
md5select md5(‘Th\000omas’::bytea); → 8ab2d3c9689aaf18b4958c334c82d8b1
rtrimselect rtrim(‘\x1234567890′::bytea,’\x9012’::bytea); → \x12345678
set_bitselect set_bit(‘\x1234567890’::bytea, 30, 0); → \x1234563890
set_byteselect set_byte(‘\x1234567890’::bytea, 4, 64); → \x1234567840
sha224select sha224(‘abc’::bytea); → \x23097d223405d8228642a477bda255b32aadbce4bda0b3f7e36c9da7
sha256select sha256(‘abc’::bytea); → \xba7816bf8f01cfea414140de5dae2223b00361a396177a9cb410ff61f20015ad
sha384select sha384(‘abc’::bytea); → \xcb00753f45a35e8bb5a03d699ac65007272c32ab0eded1631a8b605a43ff5bed8086072ba1e7cc2358baeca134c825a7
sha512select sha512(‘abc’::bytea); → \xddaf35a193617abacc417349ae20413112e6fa4e89a97ea20a9eeee64b55d39a2192992a274fc1a836ba3c23a3feebbd454d4423643ce80e2a9ac94fa54ca49f
substringselect substr(‘\x1234567890’::bytea, 3, 2); → \x5678
convertselect convert(‘text_in_utf8’, ‘UTF8’, ‘LATIN1’); →\x746578745f696e5f75746638
convert_fromselect convert_from(‘text_in_utf8’, ‘UTF8’); → text_in_utf8
convert_toselect convert_to(‘some_text’, ‘UTF8’); → \x736f6d655f74657874
encodeselect encode(‘123\000\001’, ‘base64’);→ MTIzAAE=
decodeselect 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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: