Search code examples
postgresql-12postgresql-8.4

behavior of storing string in bytea across postgresql-8 & postgresql-12

currently I am migrating the postgres database from pg8 to pg12, but I have encounter with a new issue

previously in pg8

 create table test (id int, data bytea);
 insert into test values (1,'hello world');
 table test;      

  id |    data
 ----+-------------
   1 | hello world
 (1 row)

Now in pg12

 create table test (id int, data bytea);
 insert into test values (1,'hello world');
 table test;

 id |           data
----+--------------------------
  1 | \x68656c6c6f20776f726c64
(1 row)

I want same behavior as pg8 as describe above. can anyone have an idea how should I achieve that.

and also why this behavior is different across different version? can anyone explain it.

Solution

  • You need to set in postgres.conf to escape the bytea output

     bytea_output = 'escape'

    See: https://www.postgresql.org/docs/14/datatype-binary.html