CODEWITHSUNDEEP
sql-serverpostgresqlbinarybase64bytea
Alex Petrachuk
Alex Petrachuk

Reputation: 498

PostgreSQL bytea to Base64 like SQL Server

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?

Upvotes: 1

Views: 3379

Answers (1)

sticky bit
sticky bit

Reputation: 37472

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

Upvotes: 2

Related Questions