18

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.

1
  • I needed to search for a specific hex char embedded in a string. Couldn't get concatenation in the WHERE clause to work, so ended up with WHERE regexp_match(myfield, 'abc\u0018') IS NOT NULL
    – mdisibio
    Commented Apr 8, 2022 at 18:50

1 Answer 1

23

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.

2
  • is there a casting or convet function in postgresql equal to x'CC'?
    – void
    Commented Apr 11, 2019 at 13:56
  • @Null you can prepend the character x, then cast to a bit type (of the appropriate length) and then to int. For example, SELECT substring('0x2ba41d8', 2)::bit(32)::int. Beware that if you use a bit type which is too short for your hex string, it will be silently truncated to fit. See stackoverflow.com/a/17214093/648162 for details.
    – qris
    Commented Dec 19, 2019 at 10:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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