psql escape backslash

All we need is an easy explanation of the problem, so here it is.

I want to escape backslash by \b so I can later on process it normally.
So far I use a command that return tab separated result which coverts nulls into \N as well (for mysql load into):

PGPASSWORD=$PASS psql -qtAX -U $USER -h $HOST -p $PORT -d $DB -AF $'\t' -P 'null=\N'

Any ideas?

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

If I’m understanding your question…

psql offers options for formatting its output (e.g., how it delimits columns and rows, how it presents null values, whether it includes a footer, whether it presents tuples only, whether it presents using its expanded format, etc.); however, I don’t believe it offers options to encode, escape, or otherwise process row/field-level data, such as replacing backslashes in stored data.

To accomplish what you seem to be trying to accomplish, I believe you’d need to use a SQL function like replace or regexp_replace, or would need to post-process your output (e.g., using sed).

Several loosely related thoughts:

  • You might want to consider an alternative to \b, as \b often denotes a backspace in backslash encodings.
  • You might want to consider an alternative to PGPASSWORD (and PASS) use, as it may be exposing your password to other users, known and otherwise (as environment variables can be extracted).
  • PGHOST, PGPORT, PGUSER, etc. may be worth checking out as possible alternatives to specifying them for psql (not a terribly important thought, though possible interesting to you).

P.S. Thanks for posting your question… Lots of great psql options there.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply