PostgreSQL - \copy 命令 [英] PostgreSQL - \copy command

问题描述

我尝试了上面的代码.我设法编译.但是，当我运行时，它给了我错误:

I tried the above code. I manage to Compile. However, when I run, it give me error:

Exception in thread "main" org.postgresql.util.PSQLException: ERROR: syntax error at or near "\"
Position: 1

它表明我运行的查询是错误的:

It shows that the query that I run is wrong:

String query ="\\COPY tmp from 'E:\\load.csv' delimiter ',';";

查询的 System.out.println 是:query string: \COPY tmp from 'E:\load.csv' delimiter ',';

我运行查询:\COPY tmp from 'E:\load.csv' delimiter ','; 在 PostgresSQL 客户端，它有效.

I run the query : \COPY tmp from 'E:\load.csv' delimiter ','; in PostgresSQL client, it works.

发生了什么?

Class.forName (driver);
conn = DriverManager.getConnection(host+dbname,user,password);
stmt = (java.sql.Statement) conn.createStatement();

//
PreparedStatement prepareUpdater = null;

conn.setAutoCommit(false);

String query ="\\COPY tmp from 'E:\\load.csv' delimiter ',';";

System.out.print("query string: "+query);

System.out.println("Query:"+query);
prepareUpdater = conn.prepareStatement(query);
prepareUpdater.executeUpdate();
prepareUpdater.close();

推荐答案

PostgreSQL COPY 语句存在两种变异——服务器端 COPY 和 psql> 侧 \copy.这两个语句具有相同的语法和非常相似的行为.有显着差异 - \copy 正在使用客户端文件系统.COPY 正在使用服务器端文件系统.psql \copy 不应作为服务器端 SQL 命令调用.它直接从 psql 或一些 bash 脚本使用.

PostgreSQL COPY statement exists in two mutation - server side COPY and psql side \copy. Both statements has same syntax and very similar behave. There are significant difference - \copy is working with client side file system. COPY is working with server side file system. psql \copy should not be called as server side SQL command. It is used directly from psql or from some bash scripts.

服务器端COPY用于大量的导出/导入操作.当它与文件系统一起工作时，它只能由具有超级用户权限的用户使用.非特权用户必须使用 stdin、stdout 目标，但应用程序必须支持 复制 API.

Server side COPY is used for massive export/import operation. When it working with file system, then it can be used only by user with super user rights. Unprivileged users has to use stdin, stdout target, but application have to support COPY API.

psql 支持它 - 因此您可以使用它来将某个表从一个表复制到另一个表:

psql supports it - so you can use it for copy some table from one table to other table:

psql -c "COPY mytab TO stdout" db1 | psql -c "COPY targettab FROM stdin" db2

对于 Java 环境，您必须使用一些支持，例如 复制管理器.请参阅如何将数据从文件复制到 PostgreSQL使用JDBC?

For Java Environment, you have to use some support like CopyManager. See how to copy a data from file to PostgreSQL using JDBC?

通过 COPY 语句导入可以比通过 INSERT 语句导入快得多 - 但依赖于一些额外的开销 - 在没有大量索引的普通表上差异会很大并且没有缓慢的触发器.如果您在表上有很多索引或更慢的触发器，那么 COPY 的加速将是微不足道的.

Import by COPY statement can be significantly faster than by INSERT statements - but in dependency on some additional overhead - The difference will be large on plain table without lot of indexes and without slow triggers. If you have lot of indexes on table or slower triggers, then the speedup from COPY will be marginal.

这篇关于PostgreSQL - \copy 命令的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！