I have a *.dump file (postgresql dump) and I would like to output my_table to my_table.csv. Is there a better way to do this than pg_restore -t my_table db.dump > my_table.txt and then writing a script to create the CSV from the output?
我有一个* .dump文件(postgresql转储),我想将my_table输出到my_table.csv。有没有比pg_restore -t my_table db.dump> my_table.txt更好的方法来执行此操作,然后编写脚本以从输出创建CSV?
1 个解决方案
#1
5
The output from pg_restore --data-only -t my_table db.dump basically is tab-separated headerless tabulated text with some comments and a few extra commands. A script to mangle it into csv with a tool like perl or awk would be pretty simple.
pg_restore --data-only -t my_table db.dump的输出基本上是制表符分隔的无标题表格文本,带有一些注释和一些额外的命令。使用perl或awk等工具将其分解为csv的脚本非常简单。
That said, personally I would:
那说,我个人会:
-
Restore the table to a temporary database created for the purpose. If the table depends on custom types, functions, sequences, etc you will need to restore them too.
将表还原到为此目的创建的临时数据库。如果表依赖于自定义类型,函数,序列等,您还需要还原它们。
-
In
psql,\copy the_table TO 'some_file.csv' WITH (FORMAT CSV, HEADER ON)在psql中,\ copy the_table TO'some_file.csv'WITH(FORMAT CSV,HEADER ON)
This way you can control the representation of nulls and lots more.
这样您就可以控制空值的表示形式以及更多。
#1
5
The output from pg_restore --data-only -t my_table db.dump basically is tab-separated headerless tabulated text with some comments and a few extra commands. A script to mangle it into csv with a tool like perl or awk would be pretty simple.
pg_restore --data-only -t my_table db.dump的输出基本上是制表符分隔的无标题表格文本,带有一些注释和一些额外的命令。使用perl或awk等工具将其分解为csv的脚本非常简单。
That said, personally I would:
那说,我个人会:
-
Restore the table to a temporary database created for the purpose. If the table depends on custom types, functions, sequences, etc you will need to restore them too.
将表还原到为此目的创建的临时数据库。如果表依赖于自定义类型,函数,序列等,您还需要还原它们。
-
In
psql,\copy the_table TO 'some_file.csv' WITH (FORMAT CSV, HEADER ON)在psql中,\ copy the_table TO'some_file.csv'WITH(FORMAT CSV,HEADER ON)
This way you can control the representation of nulls and lots more.
这样您就可以控制空值的表示形式以及更多。