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?

sticky bit · Accepted Answer · 2019-01-15 20:56:43Z

2

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');

answered Jan 15, 2019 at 20:56

sticky bit

37.7k12 gold badges34 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

PostgreSQL bytea to Base64 like SQL Server

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related