I have a table foo with column bar
foo
---
bar
I use Postgres's Copy command COPY (select * from foo) TO 'complete_file_path' WITH Delimiter ',' CSV HEADER
Now, how to pass a custom row delimiter? Example
row delimiter = _a_\n
I require the follow output in my file
bar_a_
somevalue_a_
somevalue_a_
somevalue_a_
I am not sure how to do this using the copy command.
Postgres COPY TO has no option to specify an alternative row delimiter. You could always do some post processing with sed or language of your choice to turn newlines into "_a_\n". or you could do some string concatenation in your select:
COPY (select bar || '_a_' as bar from foo)
TO 'complete_file_path'
WITH Delimiter ',' FORMAT CSV HEADER;
I can't help but wonder, why? If you're worried about embedded new-lines in what is getting extracted, then I'd recommend switching to the TEXT format, which will escape any embedded newlines as the two characters "\n" leaving any actual new line characters as being unambiguous record separators.
One down-side of TEXT is that you don't get the headers :-(
One way around that is to CSV extract with HEADERS and LIMIT 0 (to just get the headers), and then do the TEXT extract. Clunky, but an option.
See https://www.postgresql.org/docs/9.4/static/sql-copy.html for details of the TEXT format.
