8

I am trying to stream data through an AWK program to a Postgres COPY command. This works great usually. However, in my data recently I have been getting long text stings containing '\.' values.

Postgres Documentation mentions this combination of characters represents the end-of-data marker, http://www.postgresql.org/docs/9.2/static/sql-copy.html, and I am getting the associated errors when trying to insert with COPY.

My question is, is there a way to turn this off? Perhaps change the end-of-data marker to a different combination of characters? Or do I have to alter/remove these strings before trying to insert using the COPY command?

DJElbow
  • 3,345
  • 11
  • 41
  • 52
  • Consider adding small sample good/bad data to your question using `{}` edit tool at top left of edit box. Hard to visualize your problem. Good luck. – shellter May 21 '14 at 18:26
  • 1
    Do note that the `\.` must be single on a line, such that the "\n\\.\n" pattern occurs. – wildplasser May 21 '14 at 19:13

1 Answers1

3

You can try to filter your data through sed 's:\\:\\\\:g' - this would change every \ in your data to \\, which is a correct escape sequence for single backslash in copy data.

But I think not only backslash would be problematic. Also newlines should be encoded by \n, carriage returns as \r and tabs as \t (tab is a default field delimiter in copy).

Tometzky
  • 22,573
  • 5
  • 59
  • 73
  • The file is tab delimited, so that won't work. But I agree this type of approach will work. However, I am hoping there is a way to tell Postgres to use a different end-of-data marker instead of '\.'. I would prefer to not alter the data at all if I don't have to. – DJElbow May 23 '14 at 15:45
  • 1
    So far I haven't found a setting that can change the end-of-data marker string, and from the responses I have seen here, it seems that this might not be possible. That being said the approach in this answer is going to work the best for my issue. I used a slightly different script, `awk '{gsub(/\\+\./,"..", $0)}{print}'`, and am replacing each '\.' with '..'. – DJElbow May 23 '14 at 17:36
  • any suggestion , pointers, https://stackoverflow.com/questions/45414536/multiple-column-copy-format-postgresql-node-js – Rizwan Patel Jul 31 '17 at 13:38