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

(1 row)

So PostgreSQL is parsing the xcc as an alias.


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';
(1 row)

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

ERROR:  invalid byte sequence for encoding "UTF8": 0xcc


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.

  • is there a casting or convet function in postgresql equal to x'CC'? – Null Apr 11 at 13:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.