Pgcrypto commands produce non-standard output

Question

Each time i run 'encrypt' or 'digest' commands my output contains multiple instances of character '\'.

See example "select encrypt('123456789012345','1234','aes');" - expected output: "\x34591627f9c8eae417fc7cbbf458592c"

whereas my output is as follows: "4Y\026'\371\310\352\344\027\374|\273\364XY,"

The same applies to digest commands; have i installed pgcrypto incorrectly?

Answer 1

The return type of encrypt is bytea, which can be represented in different ways. Your two different outputs are merely two representations of the same value (mind the weird escaping in the “escape” format):

test=# select (bytea E'4Y\\026''\\371\\310\\352\\344\\027\\374|\\273\\364XY,') =
test-#        (bytea '\x34591627f9c8eae417fc7cbbf458592c') as eq;
 eq 
----
 t
(1 row)

On my installation of PostgreSQL, "hex" format is the default one, but since that's not true for you, you can set bytea_output, which controls the output of byte values:

test=# select encrypt('123456789012345','1234','aes');
                   encrypt                   
---------------------------------------------
 4Y\026'\371\310\352\344\027\374|\273\364XY,
(1 row)

test=# set bytea_output = 'hex';
SET
test=# select encrypt('123456789012345','1234','aes');
              encrypt               
------------------------------------
 \x34591627f9c8eae417fc7cbbf458592c
(1 row)

You can also explicitly encode the bytes to get a text value:

test=# select encode(encrypt('123456789012345','1234','aes'), 'hex');
              encode              
----------------------------------
 34591627f9c8eae417fc7cbbf458592c
(1 row)