带行分隔符的postgres复制 [英] postgres copy with row delimiter

问题描述

我有一个带列栏的表foo

I have a table foo with column bar

foo
---
bar

我使用Postgres的复制命令
COPY（从foo中选择*）到'complete_file_path'WITH Delimiter' ，'CSV HEADER

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 复制到没有选择指定替代行定界符的选项。您始终可以使用自己选择的sed或语言进行一些后期处理，以将换行符转换为 _ a_\n 。或者您可以在选择中进行一些字符串连接：

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;

这篇关于带行分隔符的postgres复制的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！