Encoding and binary functions ​

Encoding functions convert between text, binary data and textual encodings such as hexadecimal or Base64. They also inspect or change individual bits and bytes.

A character encoding interprets bytes as characters; a representation encoding such as hex renders bytes for display. Use convert_to/convert_from for the former and encode/decode for the latter.

Example: Converting text to bytes and back ​

SELECT encode(convert_to('café', 'UTF8'), 'hex'),
       convert_from(decode('636166c3a9', 'hex'), 'UTF8')
FORMAT csv;

"636166c3a9","café"

bit_count ​

Count set bits in binary input.

Syntax ​

bit_count(bytes)

Arguments ​

Native BLOB or UTF-8 text; embedded zero bytes are retained. SQL NULL propagates.

Returned value ​

Nonnegative INTEGER count; empty input yields zero.

Example ​

SELECT bit_count(x'0fff') FORMAT csv;

12

bit_length ​

Return UTF-8 text or exact BLOB length in bits.

Syntax ​

bit_length(value)

Arguments ​

Text conversions use native scalar rules; BLOB counts raw bytes without decoding. Embedded NUL bytes are included.

Returned value ​

INTEGER bit count or SQL NULL.

Example ​

SELECT bit_length('é') FORMAT csv;

16

convert ​

Convert bytes from one supported character encoding to another.

Syntax ​

convert(bytes,source_encoding,destination_encoding)

Arguments ​

BLOB or UTF-8 text. Reuses UTF8/SQL_ASCII, LATIN1, WIN1250..1258, KOI8R/U mappings. Native SQL_ASCII follows strict UTF8. Undefined bytes, NUL, unsupported encodings and unrepresentable characters error. SQL NULL propagates.

Returned value ​

BLOB destination bytes.

Example ​

SELECT encode(convert(decode('e9','hex'),'LATIN1','UTF8'),'hex') FORMAT csv;

"c3a9"

convert_from ​

Decode bytes into native UTF-8 text without replacements.

Syntax ​

convert_from(bytes,encoding)

Arguments ​

Same encoding set as convert_to. Text arguments provide UTF-8 input bytes; BLOB retains exact bytes. Undefined bytes, invalid UTF8 and NUL error. SQL_ASCII still requires valid native UTF8 output.

Returned value ​

TEXT or SQL NULL.

Example ​

SELECT convert_from(x'48656c6c6f','WIN1251') FORMAT csv;

"Hello"

convert_to ​

Encode text into bytes without replacement characters.

Syntax ​

convert_to(text,encoding)

Arguments ​

Supported: UTF8, SQL_ASCII, LATIN1, WIN1250 through WIN1258, KOI8R and KOI8U. Names ignore case, hyphens and underscores; WINDOWS aliases are accepted. Native text is UTF-8. NUL and unrepresentable characters error.

Returned value ​

BLOB or SQL NULL.

Example ​

SELECT encode(convert_to('Hello','WIN1251'),'hex') FORMAT csv;

"48656c6c6f"

decode ​

Decode text representations into bytes.

Syntax ​

decode(text,format)

Arguments ​

Formats: base64, hex, escape. Invalid encodings error; permitted Base64/hex whitespace follows PostgreSQL forms.

Returned value ​

BLOB; SQL NULL propagates.

Example ​

SELECT encode(decode('YWJj','base64'),'hex') FORMAT csv;

"616263"

encode ​

Represent exact input bytes as text.

Syntax ​

encode(bytes,format)

Arguments ​

Text supplies UTF-8 bytes; BLOB retains bytes. Formats: base64, hex, escape. Base64 wraps at 76 columns.

Returned value ​

TEXT; SQL NULL propagates.

Example ​

SELECT encode(x'616263','base64') FORMAT csv;

"YWJj"

get_bit ​

Read one bit using zero-based positions and low-bit-first order inside each byte.

Syntax ​

get_bit(bytes,index)

Arguments ​

Native BLOB or UTF-8 text. Signed integer index must identify an existing bit. SQL NULL propagates.

Returned value ​

INTEGER zero or one; invalid positions raise INVALID_ARGUMENT.

Example ​

SELECT get_bit(x'01',0) FORMAT csv;

1

get_byte ​

Read a byte at a zero-based position.

Syntax ​

get_byte(bytes,index)

Arguments ​

Text supplies UTF-8 bytes; BLOB supplies exact bytes. Index is a signed integer and must identify an existing byte; NULL propagates.

Returned value ​

INTEGER from zero through 255.

Example ​

SELECT get_byte(x'00ff',1) FORMAT csv;

255

set_bit ​

Return a copied binary value with one existing bit replaced.

Syntax ​

set_bit(bytes,index,value)

Arguments ​

Native BLOB or UTF-8 text; zero-based bit index and replacement zero/one. Low-bit-first order within bytes. SQL NULL propagates.

Returned value ​

BLOB preserving all other bytes/bits; invalid position/value raises INVALID_ARGUMENT.

Example ​

SELECT encode(set_bit(x'00',7,1),'hex') FORMAT csv;

"80"

set_byte ​

Copy bytes and replace one zero-based byte.

Syntax ​

set_byte(bytes,index,new_value)

Arguments ​

Same byte/index contract as get_byte. New value is an Int32 whose low eight bits are used; source remains unchanged. Output is subject to request limits.

Returned value ​

BLOB or SQL NULL.

Example ​

SELECT encode(set_byte(x'00ff',1,65),'hex') FORMAT csv;

"0041"

to_bin ​

Format an integer in binary.

Syntax ​

to_bin(integer)

Arguments ​

Native exact integers through 256 bits. Negative values use the declared integer width, or runtime width when no declaration is bound. Ordinary INTEGER inputs use 64 bits. SQL NULL propagates.

Returned value ​

TEXT two's-complement binary representation.

Example ​

SELECT to_bin(10) FORMAT csv;

"1010"

to_hex ​

Render an exact signed Int64 or unsigned UInt64 in hexadecimal.

Syntax ​

to_hex(integer)

Arguments ​

Negative signed arguments use 64-bit two's complement; oversized signed fields error; NULL propagates.

Returned value ​

Lowercase hexadecimal TEXT without a prefix.

Example ​

SELECT to_hex(2147483647) FORMAT csv;

"7fffffff"

to_oct ​

Format an integer in octal.

Syntax ​

to_oct(integer)

Arguments ​

Native exact integers through 256 bits. Negative values use the declared integer width, or runtime width when no declaration is bound. Ordinary INTEGER inputs use 64 bits. SQL NULL propagates.

Returned value ​

TEXT two's-complement octal representation.

Example ​

SELECT to_oct(10) FORMAT csv;

"12"

Hexadecimal and Unicode conversion ​

hex(value) returns uppercase hexadecimal bytes; unhex(text[,ignored_characters]) decodes hexadecimal text and returns NULL for malformed input. char(codepoint,...) constructs text; unicode(text) reads its first Unicode code point. zeroblob(length) constructs zero bytes.

SELECT hex('AB'),hex(unhex('4142')),unicode('A'),char(65,66),length(zeroblob(3)) FORMAT csv;

"4142","4142",65,"AB",3