PostgreSQL bytea to Base64 like SQL Server

Question

In SQL Server I convert binary to Base64 like this

SELECT HASHBYTES('MD5', 'Test') FOR XML PATH(''), TYPE

and have result "DLxmEfVUC9CAmjiNyVphWw==".

In PostgreSQL i have

SELECT encode(md5('Test')::bytea, 'base64')

and result is "MGNiYzY2MTFmNTU0MGJkMDgwOWEzODhkYzk1YTYxNWI=".

How can I get result like SQL Server?

score 2 · Accepted Answer · answered Jan 15 '19 at 20:56

Postgres' md5() returns a text containing the hexadecimal representation of the hash. By simply casting it to bytea you get a bytea for that string, not for the value it represents. You can use decode() to get the bytea value a string represents in hexadecimal notation.

SELECT encode(decode(md5('Test'), 'hex'), 'base64');

PostgreSQL bytea to Base64 like SQL Server

1 Answers1