MySQL's HEX() and UNHEX() equivalent in Postgres?

Question

I'm in the process of converting some tools over that are using MySQL to PostgreSQL. With that, I've run into a number of issues but was able to find most everything. The one I'm having an issue with is HEX() and UNHEX(). I've tried encode(%s, 'hex') and decode(%s, 'hex') which did actually stop causing me to have errors, but it still did not seem to do the trick. Does anyone have an idea to what the equivalent of those functions would be in Postgres?

Here is the old MySQL query:

SELECT HEX(test_table.hash),
       title,
       user,
       reason,
       description,
       url,
       performed,
       comment,
       authenticated,
       status
FROM alerts
JOIN user_responses ON test_table.hash = user_responses.hash
JOIN test_status ON test_table.hash = test_status.hash
WHERE status = %s

And here is my updated query in PostgreSQL format:

SELECT encode(test_table.hash, 'hex') as hash,
       title,
       user,
       reason,
       description,
       url,
       performed,
       comment,
       authenticated,
       status
FROM test_table
JOIN user_responses ON test_table.hash = user_responses.hash
JOIN test_status ON test_table.hash = test_status.hash
WHERE status = %s

Thanks!

Answer 1

create function hex(text) returns text language sql immutable strict as $$
  select encode($1::bytea, 'hex')
$$;

create function hex(bigint) returns text language sql immutable strict as $$
  select to_hex($1)
$$;

create function unhex(text) returns text language sql immutable strict as $$
  select encode(decode($1, 'hex'), 'escape')
$$;


select hex('abc'), hex(123), unhex(hex('PostgreSQL'));

Result:

╔════════╤═════╤════════════╗
║  hex   │ hex │   unhex    ║
╠════════╪═════╪════════════╣
║ 616263 │ 7b  │ PostgreSQL ║
╚════════╧═════╧════════════╝

It is PostgreSQL: everything possible :)

Answer 2

I recommend checking out the mysqlcompat library for Postgres, it includes a whole bunch of MySQL functions ported over to Postgres.

You can see their implementations of the HEX or UNHEX functions. Note in particular that MySQL's hex function has two different modes of working:

If the argument is a string, each character in the argument is converted to two hexadecimal digits.

If the argument is decimal, the function returns a hexadecimal string representation of the argument and treated as a longlong(BIGINT) number.

So if you roll your own, make sure you support all the possible use-cases, the way mysqlcompat does.