How do I write a hex literal in PostgreSQL?

Question

How do you write a hex literal in PostgreSQL? Like say I want 0xCC, if I do;

SELECT 0xCC;
 xcc 
-----
   0
(1 row)

So PostgreSQL is parsing the xcc as an alias.

Answer 1

Arbitrary Data / Integers (int)

You can write the hexidemical byte using Bit-string constants

SELECT x'CC';  -- same as b'11001100'

Which is essentially the same as bit x'CC' returning a Bit String Type but there is a cast available to int so you can do x'CC'::int * 5

UTF-8 Byte Sequence

If the byte sequences is a valid UTF-8 character, you can also use E'' with a backslash-escape sequence (single backslash \)

SELECT E'\x41';
 ?column? 
----------
 A
(1 row)

If the sequence is invalid, you'll get an error

# SELECT E'\xCC';
ERROR:  invalid byte sequence for encoding "UTF8": 0xcc

bytea-specific.

PostgreSQL has a variable-length binary data type that allows arbitrary bytes. This type is called bytea. You can move into this format using the bytea hex format. In this we either use,

• Use a double backslash \\
• Use the cast to bytea from from the hex representation.

Here are both syntax,

SELECT bytea E'\\xDEADBEEF', bytea '\xDEADBEEF';
   bytea    |   bytea    
------------+------------
 \xdeadbeef | \xdeadbeef
(1 row)

Storing large numbers

If you only need to store large numbers, rather than storing them as bytea I would check out pg_bignum which stores them using the openssl implementation of Big Numbers.