PostgreSQL - \copy command

Question

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 ',';";

The System.out.println for the query is: query string: \COPY tmp from 'E:\load.csv' delimiter ',';

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

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();

the actual command that I need to run is \COPY if I put COPY only, I can load the .csv file to table - error must be a super user — , May 27 '15 at 02:36
\COPY Is a psql internal command, not an SQL command. Only psql understands it. — Sami Kuhmonen, May 27 '15 at 02:57

score 2 · Answer 1 · edited May 23 '17 at 12:19

2

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.

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 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

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

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.

edited May 23 '17 at 12:19

Community

1
1

answered May 27 '15 at 05:41

Pavel Stehule

42,331
5
91
94

The OP is very obviously about working in a Windows operating system with Java, so an example using `psql` with `stdin` and `stdout` is irrelevant. – Patrick May 27 '15 at 05:48
This `Unprivileged users has to use stdin, stdout target` is very helpful while most posts only says superuser rights are required. Is there any official document about this? Thanks. – Richard Jun 02 '23 at 08:47
1

@Richard https://www.postgresql.org/docs/current/sql-copy.html – Pavel Stehule Jun 03 '23 at 05:33
@PavelStehule Thank you. copy the part here hoping it helps other people. `COPY naming a file or command is only allowed to database superusers or users who are granted one of the roles` ...... `Do not confuse COPY with the psql instruction \copy. \copy invokes COPY FROM STDIN or COPY TO STDOUT, and then fetches/stores the data in a file accessible to the psql client. Thus, file accessibility and access rights depend on the client rather than the server when \copy is used.` – Richard Jun 03 '23 at 14:12

score 0 · Answer 2 · answered May 28 '15 at 06:45

Thank you for your feedback.

I managed to load *.csv using CSVReader.

I download CSVReader package and include it in my codes.

Its working fine.

 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

Your usage of the `PreparedStatement` is completely wrong (and inefficient). You should only prepare it **once** before the while loop using placeholders. Then inside the loop use `setString()` to provide the actual values before calling `executeUpdate()`. — , May 28 '15 at 06:57

score 0 · Answer 3 · answered May 10 '16 at 11:07

0

You use COPY instead of /COPY

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

It will work

answered May 10 '16 at 11:07

apm

525
6
19

score -2 · Answer 4 · answered May 27 '15 at 02:58

-2

The COPY SQL command reads a file from the local file system and the server process must have access to it.

The \copy command is a psql instruction and is only available within that environment. You cannot use that in a SQL query.

Since Java has a good CSV reader class, you could simply read the file in your code and then use individual INSERT commands to load the data in the database. This is effectively what \copy does when working in psql.

answered May 27 '15 at 02:58

Patrick

29,357
6
62
90

2

Individual INSERTS will be significantly slowly than COPY. Usually it depends on size, but for massive import it is not good advice. – Pavel Stehule May 27 '15 at 05:27
@PavelStehule That is very true but not a solution to the question. And I do not read anything in the OP that suggests this might be a massive csv file. – Patrick May 27 '15 at 05:33
I disagree with sentence "This is effectively what \copy does when working in psql" - sure it depends on details - but it is confusing little bit – Pavel Stehule May 27 '15 at 05:45
Also COPY is supporting inserting into a table. Check COPY table_name FROM 'filename' – frlan May 27 '15 at 12:22

PostgreSQL - \copy command

4 Answers4

Linked