CODEWITHSUNDEEP
postgresqljdbc
babcoccl
babcoccl

Reputation: 169

Can PostgreSQL COPY read CSV from a remote location?

I've been using JDBC with a local Postgres DB to copy data from CSV files into the database with the Postgres COPY command. I use Java to parse the existing CSV file into a CSV format matches the tables in the DB. I then save this parsed CSV to my local disk. I then have JDBC execute a COPY command using the parsed CSV to my local DB. Everything works as expected. Now I'm trying to perform the same process on a Postgres DB on a remote server using JDBC. However, when JDBC tries to execute the COPY I get 

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

Am I correct in understanding that the COPY command tells the DB to look locally for this file? I.E. the remote server is looking on its C: drive (doesn't exist).

If this is the case, is there anyway to indicate to the copy command to look on my computer rather than "locally" on the remote machine? Reading through the copy documentation I didn't find anything that indicated this functionality.

If the functionality doesn't exist, I'm thinking of just populating the whole database locally then copying to database to the remote server but just wanted to check that I wasn't missing anything.

Thanks for your help.

Upvotes: 5

Views: 7911

Answers (3)

Tim Pote
Tim Pote

Reputation: 28049

To my knowledge, the COPY command can only be used to read locally (either from stdin or from file) from the machine where the database is running.

You could make a shell script where you run you the java conversion, then use psql to do a \copy command, which reads from a file on the client machine.

Upvotes: 1

Frank Farmer
Frank Farmer

Reputation: 39366

Create your sql file as follows on your client machine

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

Then execute, on your client

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

Upvotes: 5

sega_sai
sega_sai

Reputation: 8548

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

Otherwise (as already noted by others) you can use \copy from psql which allows accessing the local files on the client machine

Upvotes: 4

Related Questions