Menu
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.
208
While using the server-side command, it will run on the server and copy to CSV on the server end. Let us consider the copy query in the below sections. Psql \copy command is used when you want to export the data from Postgres table to a CSV file on a client machine. To use this command, you will need access to the psql prompt.
Copy data from a CSV file to remote database. The following command copies data from a local CSV file to a remote PostgreSQL database psql -h remotehost -d your_primary_db -U postgres -c "copy users (id, email, first_name, last_name) from '/tmp/users.csv' with delimiter as ','" Copy data using STDIN to a remote database.
) ENCODING ' encoding_name ' COPY moves data between PostgreSQL tables and standard file-system files. COPY TO copies the contents of a table to a file, while COPY FROM copies data from a file to a table (appending the data to whatever is in the table already). COPY TO can also copy the results of a SELECT query.
There are two different variants of the command, one for the client-side and the other for the server-side. When we are using the command for the client-side import/export, it will export a PostgreSQL table to CSV and save it on the client computer. While using the server-side command, it will run on the server and copy to CSV on the server end.
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
181
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
