Different result formats when insert postgreSQL function result between JDBC & console

Question

In postgreSQL, I use pgcrypto module to call pgp_sym_encrypt function (return bytea type) and save the result into a text column:

For example I have test table with column columnA(text):

CREATE EXTENSION pgcrypto;
insert into test (columnA) values (pgp_sym_encrypt('test','test'));

If I run in console, the result is similar to (escaped binary):

\303\015\004\007\003\002\022\261B\015\376\235\023\010j\3225\001\244l\253\332\026\037\\Q\305\253\365H\264\222\021\233\345\326\036Ma\346vwq\373\201\303\300\000\303\354\327:\017\020\036Q\201\025\210\364%\215$\017\304Y^_&\267

If I run in JDBC, the result is similar to (hex format):

public class Main {
    public static void main(String[] args) throws Exception {
        Class.forName("org.postgresql.Driver");
        Connection connection = DriverManager.getConnection("jdbc:postgresql://localhost:5432/db", "postgres", "postgres");
        Statement stmt = connection.createStatement();
        String sql = "insert into test (columnA) values (pgp_sym_encrypt('test','test'))";
        stmt.executeUpdate(sql);
        connection.close();
    }
}

Result:

\xc30d040703026859a0885a496bc66bd240018d9cbede4805fc5b733afc706d061ae613600962222f008ab2fc4e99cc1c87841e3929833066ba42697784276c49efa67655a399fa9f77264769a42eb7c85e

If I use pgp_sym_decrypt to decrypt, both are decrypted fluently, but I wonder why the results are in different formats. If I want the same format, how can I do?

UPDATE: I know they are escaped format & hext format and can be set as @Vao Tsun answer. But I wonder why, I thought they have to use same default format? Did the JDBC override format? Is there any config for JDBC for this default format or I have to call the set statement in every transaction?

Answer 1

It's the matter of your client settings:

so=# set bytea_output to escape;
SET    
so=# select '\xc30d040703026859a0885a496bc66bd240018d9cbede4805fc5b733afc706d061ae613600962222f008ab2fc4e99cc1c87841e3929833066ba42697784276c49efa67655a399fa9f77264769a42eb7c85e'::bytea;
                                                                                                          bytea
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 \303\015\004\007\003\002hY\240\210ZIk\306k\322@\001\215\234\276\336H\005\374[s:\374pm\006\032\346\023`\011b"/\000\212\262\374N\231\314\034\207\204\0369)\2030f\272Biw\204'lI\357\246vU\243\231\372\237w&Gi\244.\267\310^
(1 row)

Time: 0.487 ms
so=# set bytea_output to hex;
SET
Time: 2.134 ms
so=# select '\xc30d040703026859a0885a496bc66bd240018d9cbede4805fc5b733afc706d061ae613600962222f008ab2fc4e99cc1c87841e3929833066ba42697784276c49efa67655a399fa9f77264769a42eb7c85e'::bytea;
                                                                                bytea
----------------------------------------------------------------------------------------------------------------------------------------------------------------------
 \xc30d040703026859a0885a496bc66bd240018d9cbede4805fc5b733afc706d061ae613600962222f008ab2fc4e99cc1c87841e3929833066ba42697784276c49efa67655a399fa9f77264769a42eb7c85e
(1 row)

https://www.postgresql.org/docs/current/static/runtime-config-client.html#GUC-BYTEA-OUTPUT

Sets the output format for values of type bytea. Valid values are hex (the default) and escape (the traditional PostgreSQL format). See Section 8.4 for more information. The bytea type always accepts both formats on input, regardless of this setting.

update

You can set this setting per user alter user vao set bytea_output to hex or per database or even per cluster, not only for session or transaction. You definitely don't have to run it before every statement if you don't want of course.

Regarding if jdbc has specific setting - I don't know, afaik you can pass client setting in connect string.

Lastly if you change it per user/db, you will need to reconnect for effect. And SET LOCAL/SESSION will overcome this parameter at once...