PostgresSQL command - \ copy
I tried the above code. I manage to compile. However, when I run it gives me an error:
Exception in thread "main" org.postgresql.util.PSQLException: ERROR: syntax error at or near "\"
Position: 1
It shows that the query I'm running is wrong:
String query ="\\COPY tmp from 'E:\\load.csv' delimiter ',';";
System.out.println
for request:
query string: \COPY tmp from 'E:\load.csv' delimiter ',';
I run a query: \COPY tmp from 'E:\load.csv' delimiter ',';
in PostgresSQL client, it works.
Any idea what happened?
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();
source to share
The PostgreSQL operator COPY
exists on two sides of the mutation - the server side COPY
and the psql
side \copy
. Both operators have the same syntax and are very similar. There are significant differences - it \copy
works with the file system on the client side. COPY
works with the file system on the server side. psql
\copy
should not be called as a server SQL command. It is used directly from psql
or from some scripts bash
.
The server side is COPY
used for massive export / import operations. When it is working with the filesystem, it can only be used by a user with superuser privileges. Unprivileged users to use stdin
, stdout
target, but the application must support the COPY the API .
psql
supports it, so you can use it to copy some table from one table to another table:
psql -c "COPY mytab TO stdout" db1 | psql -c "COPY targettab FROM stdin" db2
For Java environment, you need to use some support like CopyManager . See How to copy data from file to PostgreSQL using JDBC?
Operator-assisted imports COPY
can be significantly faster than operators INSERT
, but subject to some additional overhead. The difference will be large on a simple table without a lot of indexes and no slow triggers. If you have many pointers on tables or slower triggers, then the speedup from COPY
will be negligible.
source to share
Thank you for your feedback.
I was able to load * .csv using CSVReader.
I download the CSVReader package and include it in my codes.
Works well.
CsvReader products = new CsvReader("/tmp/ip2location/IP-COUNTRY-REGION-CITY-LATITUDE-LONGITUDE-ISP-DOMAIN-MOBILE-USAGETYPE.CSV");
products.readHeaders();
while (products.readRecord())
{
String ip_from = products.get("ip_from"); //int
String ip_to = products.get("ip_to"); //int
PreparedStatement prepareStat = null;
String sqlIinsert = "insert into ip2location_tmp(ip_from, ip_to )"
+ " VALUES ("+ip_from+","+ip_to+");";
System.out.println("sqlInsert:"+sqlIinsert);
prepareStat = conn.prepareStatement(sqlIinsert);
prepareStat.executeUpdate();
}
products.close();
}enter code here
source to share
COPY
The SQL command reads a file from the local file system and the server process must be able to access it.
The command \copy
is an instruction psql
and is only available in this environment. You cannot use this in a SQL query.
Since Java has a nice CSV reader class, you can simply read the file in your code and then use separate commands INSERT
to load the data into the database. It is effective \copy
when working in psql
.
source to share