CODEWITHSUNDEEP
postgresqlescaping
Paulb
Paulb

Reputation: 1531

Escaping Backslashes in Postgresql

I need to write a file to disk from postgres that has character string of a backslash immediately followed by a forward slash \/

Code similar to this has not worked:

drop table if exists test;
create temporary table test (linetext text);
insert into test values ('\/\/foo foo foo\/bar\/bar');
copy (select linetext from test) to '/filepath/postproductionscript.sh';

The above code yields \\/\\/foo foo foo\\/bar\\/bar ... it inserts an extra backslash.

When you view the temp table, the string is correctly viewed as \/\/, so I am not sure where or when the text is changed into \\/\\/

I've tried doubling the \, variations of E before the string, and quote_literal() without luck.

I have note found a solution here Postgres Manual

Running Postgres 9.2, encoded UTF-8.

Upvotes: 2

Views: 6736

Answers (2)

wildplasser
wildplasser

Reputation: 44250

The \ excaping is only recognised if the stringliteral is prefixed with E , otherwise the standard_conforming_strings setting (or the like) is respected (ANSI-SQL has a different way of string escaping, probably stemming from COBOL;-).

drop table if exists test;
create temporary table test (linetext text);
insert into test values ( E'\/\/foo foo foo\/bar\/bar');
copy (select linetext from test) to '/tmp/postproductionscript.sh';

UPATE: an ugly hack is to use .csv format and still use \t as delimter. The #!/bin/sh as a shebang headerline should be consdered a feature 

       -- without a header line
drop table if exists test;
create temporary table test (linetext text);
insert into test values ( '\/\/foo foo foo\/bar\/bar');
copy (select linetext AS "#linetext" from test) to '/tmp/postproductionscript_c.sh'
        WITH CSV
        DELIMITER E'\t'
        ;
        -- with a shebang header line
drop table if exists test;
create temporary table test (linetext text);
insert into test values ( '\/\/foo foo foo\/bar\/bar');
copy (select linetext AS "#/bin/sh" from test) to '/tmp/postproductionscript_h.sh'
        WITH CSV
        HEADER
        DELIMITER E'\t'
        ;

Upvotes: 0

Peter Eisentraut
Peter Eisentraut

Reputation: 36759

The problem is that COPY is not intended to write out plain-text files. It is intended to write out files that can be read back by COPY. And the semi-internal encoding that it uses does some backslash escaping.

For what you want to do, you need to write some custom code. Either use a normal client library to read the query results and write them to a file, or, if you want to do it in-server, use something like PL/Perl or PL/Python.

Upvotes: 2

Related Questions