Geek asks and answers

Can PostgreSQL COPY read CSV from a remote location?

I use JDBC with local Postgres mail to copy data from CSV files to the database using the Postgres COPY command. I use Java to parse an existing CSV file in CSV format that matches the tables in the database. Then I save this parsed CSV to my local drive. Then I JDBC execute the COPY command using the parsed CSV for my local DB. Everything works as expected. Now I am trying to execute the same process in a Postgres database on a remote server using JDBC. However, when JDBC tries to execute COPY, I get

org.postgresql.util.PSQLException: ERROR: could not open file "C:\data\datafile.csv" for reading: No such file or directory

Do I understand correctly that the COPY command tells the database to look local to this file? I.E. the remote server is looking at its C: drive (does not exist).

If so, does it still tell the copy command to view it on my computer, and not "locally" on the remote computer? After reading the documentation on the copies, I did not find anything that would indicate this functionality.

If the functionality does not exist, I am thinking of just populating the entire database locally and then copying it to the database on the remote server, but I just wanted to verify that I did not miss anything.

Thank you for your help.

+5
source share
3 answers

If you use JDBC, the best solution for you is to use the PostgreSQL COPY API http://jdbc.postgresql.org/documentation/publicapi/org/postgresql/copy/CopyManager.html

( ) \copy psql,

+4

sql

COPY testtable (column1, c2, c3) FROM STDIN WITH CSV;
1,2,3
4,5,6
\.

psql -U postgres -f/mylocaldrive/copy.sql -h remoteserver.example.com

+5

As far as I know, the command COPYcan only be used to read locally (either from stdin or from a file) from the computer on which the database is running.

You can create a shell script where you run java conversion and then psqlexecute a command \copythat is read from a file on the client machine.

+1
source

More articles:


All Articles