Postgres psql output strings without escape characters

Question

I have a column in PostgreSQL that is of type bytea that usually has text data. I want to get the value of that column for a certain row with newlines and tabs intact rather than the octal escape characters that psql is outputting. For example, I run:

psql -Atc 'SELECT my_column from my_table limit 1;'

And I get output like:

Foo\015\012Bar\011This is some text.

Instead I want:

Foo
Bar This is some text.

I realize I can just use grep, which is what I'm doing, but I'm wondering if there's some simple way to do this via psql. I tried type casting the value to type text, but that didn't seem to help.

And if you have text but want bytea, see stackoverflow.com/questions/18283973/… — Bryce
– Bryce, Commented Aug 17, 2013 at 0:46

Skiv_mag · Accepted Answer · 2013-01-10 11:50:53Z

4

Try to convert your bytea in this way to ascii:

psql -Atc "SELECT convert_from (my_column, 'SQL_ASCII') from my_table limit 1;"

answered Jan 10, 2013 at 11:50

Skiv_mag

2842 gold badges4 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Erwin Brandstetter Over a year ago

Clever solution. Note, however, that non-printable characters will create very "creative" outcome this way. Sqlfiddle to play with ...

Collectives™ on Stack Overflow

Postgres psql output strings without escape characters

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related