我通过终端中的psql对远程postgresql数据库运行以下查询:

\copy (SELECT cylinders.reduction, cylinders.name, print_suppliers.name FROM cylinders, jobs, print_suppliers WHERE cylinders.id = jobs.cylinder_id AND print_suppliers.id = cylinders.print_supplier_id AND jobs.id = 015983) TO '/Users/alex/result_sql.csv' WITH CSV HEADER

并将正确的csv文件保存到我的hd上。但是,我需要在本地保存一个xml文件,并尝试执行以下操作:
\copy SELECT query_to_xml('SELECT cylinders.reduction, cylinders.name, print_suppliers.name FROM cylinders, jobs, print_suppliers WHERE cylinders.id = jobs.cylinder_id AND print_suppliers.id = cylinders.print_supplier_id AND jobs.id = 015983', true, true, '') TO '/Users/alex/result_sql.xml'

但我有个错误:
\复制:“查询到xml”时出现分析错误。没有副本…给,我是
获得完美的xml输出。
有没有办法将终端的xml输出本地保存到我的计算机上?
谢谢,亚历克斯

最佳答案

复制后需要额外的括号才能从查询复制,而不是从表复制。比如这里:

\copy (
SELECT query_to_xml(
 'SELECT cylinders.reduction, cylinders.name, print_suppliers.name
  FROM cylinders, jobs, print_suppliers
  WHERE cylinders.id = jobs.cylinder_id AND print_suppliers.id = cylinders.print_supplier_id AND jobs.id = 015983'
 , true, true, ''
  )
)
TO '/Users/alex/result_sql.xml'

10-07 12:39