Extract clob postgres value in psql command-CodePudding

With theses row in my table :

From intellij, when I run my PSQL request, lo_get result is correctly return (here a JSON content)

Select lo_get(cast(my_col as bigint)) from my_table

my_column is "text" format

But when i execute the same request in psql console on my postgres server, the result is not correctly return

example : \x7b22646174654c6976726169736f6e223a22323032322d30322d32325432333a30303a30302e3030302b30303030222c22666f726d617443616e74696e6573223a5b5d2c226d6f6e74616e7450616e696572456e436f757273223a307d

Is there a way to get these value in psql columns ?

CodePudding user response：

If I encode it in 'escape' method, I get something which looks like JSON, but which doesn't look anything at all like what you show in your image (which does not look like JSON in the least).

select encode('\x7b22646174654c6976726169736f6e223a22323032322d30322d32325432333a30303a30302e3030302b30303030222c22666f726d617443616e74696e6573223a5b5d2c226d6f6e74616e7450616e696572456e436f757273223a307d'::bytea,
    'escape');

If you want to always encode it like this, you could set bytea_output TO escape. But while that might make sense in the psql interactive tool, it probably isn't preferable as a permanent server setting, i.e. when dealing with other clients.