Postgresql – COPY TO statement in postgres outputting double backslashes

postgresql

I would like to output a string with backslashes that is enclosed in dollar quotes to a file in postgres. For example, with the following query:

COPY (select $$a\x5cb\x5cc$$ ) to $$c:\test.txt$$;

I would expect the contents of the file to be

a\b\c

consistent with the documentation from Postgres suggesting that these string literals should be, well, literals:

Notice that inside the dollar-quoted string, single quotes can be used without needing to be escaped. Indeed, no characters inside a dollar-quoted string are ever escaped: the string content is always written literally. Backslashes are not special, and neither are dollar signs, unless they are part of a sequence matching the opening tag.

However, to my surprise, the contents of this file are instead

a\\b\\c

Why is this the case? How can I output a literal string to a file without using single

Best Answer

It doesn't matter how you got the string into COPY TO. The output of COPY TO is defined on its own terms.

Read the "Text Format" section of the docs for the command you are using.

The output of COPY TO is not inside the dollar quotes strings, so the rules of those strings does not matter.

You can use with (format csv); option to COPY. It will consider a different set of characters to be special, and will escape those special characters in a different way. It will not double the backslashes, but might do something else you don't like instead.

Related Question