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?

  • 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

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
| improve this answer | |
  • 2
    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

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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