从pg中导出数据到csv文件

导出命令

利用copy命令，这里需要用\copy，copy的话会有权限的问题。

psql -h127.0.0.1 -p5432 -Upostgres -W -dmy_db_name -t -A -F"," -c "\copy (select * from t_test) to 'test.csv' with (format csv,header true,delimiter ',');"

优化

boolean数据类型

目前copy输出到csv的时候，默认bool类型的数据，True为t，False为f。如果有需要csv中为True和False的话，可以在sql语句中把bool类型转为text。

psql -h127.0.0.1 -p5432 -Upostgres -W -dmy_db_name -t -A -F"," -c "\copy (select id,field_bool::text from t_test) to 'test.csv' with (format csv,header true,delimiter ',');"

导出json

默认导出json类型数据的时候，会是这样的格式：

"{""rate"": 0.765432}"

这样的格式是为了符合csv的格式。如果想要符合json格式的话，可以加上参数``，具体如：

psql -h127.0.0.1 -p5432 -Upostgres -W -dmy_db_name -t -A -F"," -c "\copy (select * from t_test) to 'test.csv' with (format csv,header true,delimiter ',',quote e'\x01');"

输入密码

如果要在脚本中写入的话，就需要自动输入密码。这里可以用设置环境变量的方法。
需要注意，这里不能加-W参数，-W是强制输入密码。

PGPASSWORD=123456 psql -h127.0.0.1 -p5432 -Upostgres -dmy_db_name -t -A -F"," -c "\copy (select id,field_bool::text from t_test) to 'test.csv' with (format csv,header true,delimiter ',');"

docker化

直接调用

docker run -it --rm postgres:11.9 psql -h127.0.0.1 -p5432 -Upostgres -W -dmy_db_name -c '\copy (select * from test) to "test.csv" with (format csv,header true,delimiter ",")'

脚本调用

因为命令行中会遇到单引号和双引号的问题，我们这里使用shell变量作为中间值来完成

conn_script="psql -h127.0.0.1 -p5432 -Upostgres -dmy_db_name"

sql=$(cat <<- EOF
select id,name
from t_test
EOF
    )
file='test.csv'
export_script="\copy ($sql) to '$file' with (format csv,header true,delimiter ',')"
script="$conn_script -c \"$export_script\""

bash -c "docker run -it --env PGPASSWORD=123456 --rm postgres:11.9 $script"

文件映射

conn_script="psql -h127.0.0.1 -p5432 -Upostgres -dmy_db_name"

sql=$(cat <<- EOF
select id,name
from t_test
EOF
)
file='/opt/my/test.csv'
export_script="\copy ($sql) to '$file' with (format csv,header true,delimiter ',')"
script="$conn_script -c \"$export_script\""

# 当前目录下的data目录，映射到/opt/my
dir="$(pwd)/data"
bash -c "docker run -it --env PGPASSWORD=123456 -v $dir:/opt/my --rm postgres:11.9 $script"

参考资料

postgresql-sql-copy