Using PostgreSQL 8.4, how to convert bytea to text value in postgres?

In my application I insert data in database using C code, since the strings I receive from an untrusted source I have escaped them using PQescapeByteaConn of libpq library. Which is working perfectly fine i.e. results in Octet format String. See below example,

Input String : \n\t\f\b\p\k\j\l\mestPrepared

Output String : \\012\\011\\014\\010pkjlmestPrepared

Output String is inserted in the database. Now I retrieve that data from the database in a java code using JDBC. How I can unescape the string back to its original value ?

I thought of two possible approaches,

• Change the database retrieval query and pass this field to any String manipulation function of postgres i.e. which can convert bytea to text.

• Do the decoding in Java code.

I can understand that the approach 1 will be more efficient. I have tried almost all the functions listed here but nothing is working. Please Help!!

I am using version 8.4 of postgres on a linux machine.

For followers, since this seems to be the canonical question for "converting bytea to text" (i.e. so you can actually see it in pgAdmin etc.) . Here's how to just get it viewable (assuming your_column_name is
type bytea)

select encode(table.your_column_name, 'escape') as your_alias_name from table_name

StackExchange Database Administrators Q#53309, answer score: 37