Postgres Escape Single and Double Quotes in Text Field

Question

I may have an odd request. I'm not finding any help via Google.

I am using the DbVisualizer Pro 10.0.15 gui tool connected to a PostgreSQL db.

I need to create a csv file from a database table. I select the records I need in a query then export the results to a .csv file. I can do that easy.

select note from notes;

highlight all results records >> right-click >> select export >> choose csv

Some of the records have both single and/or double-quotes in the content.

The person receiving this file needs to upload the csv file into another system. They are stating that these single and double-quotes in the content will not work in their upload. I've been asked to escape these quotes. They want to keep them in the content, but have them appear in the field with the backslash escape character, i.e: it is John's ball would show in the csv file as: it is John\'s ball. The same for dbl-quotes.

I could probably do this with a search-and-replace function in a text editor after creating the csv file, but I'd like to think this can be done via sql.

I've tried playing with the regexp_replace() function.

select regexp_replace(note, '"', '\"') as notes from notes works on the dbl-quotes, but I'm not having any luck on the single quotes.

Help? Is there a way to do this?

Solution

You can escape double quotes by doing:

postgres=# SELECT REGEXP_REPLACE('this "is" a string', '"', '\"', 'g');
     regexp_replace    
  ----------------------
  this \"is\" a string
  (1 row)

For single quotes, the approach is similar, but you have to escape them using another single quote. So instead of having something like /', it should be ''. The query is:

postgres=# SELECT REGEXP_REPLACE('this ''is'' a string', '''', '\''', 'g');
    regexp_replace    
----------------------
 this \'is\' a string
(1 row)

Note the 'g' flag in the end, this forces it to replace all occurrences and not just the first one found.

You can also replace both single and double quotes in a single statement, although they are replaced with the same string (\" in this case).

postgres=# SELECT REGEXP_REPLACE('this "is" a ''normal'' string', '["'']', '\"', 'g');
         regexp_replace          
---------------------------------
 this \"is\" a \"normal\" string
(1 row)

Comments

[GRoutar]

How is the csv being uploaded into the other system? You can avoid escaping all instances if that person escapes them during the upload. E.g. If you write csv files into a database using Python's csv library, the escaping is done automatically without having to worry about doing it manually.

[John Cowan]

Thanks, ... Yeah, we tried suggesting that. The file is sent to someone outside our department/school. The person either does not want to do it or does not know how or something else, so it's on me. We have no control over how they do that.

[John Cowan]

Yes, YaY!!! That worked. I had the dbl-quotes, but the single was killing me. I had the first part of the regex ''', but the replace-with was giving me fits. Thank you, so much