Escaping Backslashes in Postgresql

Question

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.

Sounds like the copy command does the escaping as the data is stored correctly in the database. Although I'm not aware of any setting for the COPY command to behave like that. — user330315
– user330315, Commented Jan 2, 2013 at 15:33
Backslash characters (\) can be used in the COPY data to quote data characters that might otherwise be taken as row or column delimiters. In particular, the following characters must be preceded by a backslash if they appear as part of a column value: backslash itself, newline, carriage return, and the current delimiter character. — Clodoaldo Neto
– Clodoaldo Neto, Commented Jan 2, 2013 at 15:45

Peter Eisentraut · Accepted Answer · 2013-01-02 15:39:48Z

2

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.

answered Jan 2, 2013 at 15:39

Peter Eisentraut

37.1k12 gold badges91 silver badges91 bronze badges

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

1 Comment

Paulb Over a year ago

You're probably right, and that is an answer I didn't want to hear. Barring other ideas, I'll probably code the long way around the issue...I'll put the backslash portions of code into a separate shell script, and I'll have postgres generate a call and pass a variable to that script. That seems to be the cleanest approach at the moment. [I wish there was a Postgres module for writing plain text files] Thanks.

wildplasser · Accepted Answer · 2013-01-02 16:57:06Z

0

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'
        ;

edited Jan 2, 2013 at 16:57

answered Jan 2, 2013 at 16:14

wildplasser

44.5k9 gold badges72 silver badges116 bronze badges

4 Comments

Paulb Over a year ago

Thanks. I tried that code, no luck, at least in my configuration. It yielded //foo foo foo/bar/bar.

wildplasser Over a year ago

But you do want both the backslash and the slash in the string? What is the original string ?(I suppose the values() thing is just for demonstration purposes)

Paulb Over a year ago

Thanks. I'll give it a look. I kept trying with your code suggestion.. and the results never worked, sometimes they were unexpected. I think Peter is right.. COPY is doing things inside for it's own purposes. I'll explore the CSV idea.

wildplasser Over a year ago

COPY is an ugly beast, mostly caused by the need for backward compatibility. Would have been better to create a new NCOPY command with a more orthogonal syntax.

Collectives™ on Stack Overflow

Escaping Backslashes in Postgresql

2 Answers 2

1 Comment

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related