Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a log file full of URLs, generated by Bro IDS.

When Bro logs a URL with non-ascii characters in it, it inserts \xYY where YY is the hexadecimal character code. Also, some URLs contain "\x".

Is there a setting or flag I can use with the COPY or \copy command to stop postgres from trying to interpret these string sequences and just put them in the text field as is?

share|improve this question
    
Please make it a habit to supply what you have tried (even if it seems trivial), your PostgreSQL version and relevant configuration data (like database encoding in this case). –  Erwin Brandstetter Mar 12 '13 at 4:26

1 Answer 1

up vote 1 down vote accepted

The backslash \ is the default escape character in the (default) text format of COPY. The format you describe is recognized as (quoting the manual here)

backslash sequences are recognized by COPY FROM
...
\xdigits | Backslash x followed by one or two hex digits specifies the character with that numeric code

.. which seems to be just as intended.
To avoid this effect you could use the CSV format instead, where the backslash has no special meaning:

COPY tbl FROM '/path/to/file/log.sql' (FORMAT csv);

Be sure to read the manual to learn about additional differences.

Alternatively you could replace every \ with \\ in your file with a sed script or something.

If the above doesn't solve your problem, please supply:

  • PostgreSQL version
  • Database encoding
  • Demo data
  • Definition of the table you COPY to
  • The exact COPY invocation you used
share|improve this answer
1  
Thanks, that worked. Just had to set the delimiter to \t a la: COPY to_table (col1,col2,col3) FROM '/path/to/file' with (FORMAT csv, DELIMITER E'\t'); –  potatohead00 Mar 12 '13 at 23:09

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.