COPY to remote databse from a local file

postgresql soci sqlbulkcopy


There are similar questions but they didn't address my problem: I have a stored procedure defined in my remote database. As part of it operation, the SP has to bulk insert from a local file (through my application running on my local machine): the procedure:

CREATE OR REPLACE FUNCTION upsert_realtime(path text, traveltime_table regclass)
    EXECUTE format('COPY temp_table(link_id,start_time,end_time,travel_time,interval_time, travel_mode) FROM %L DELIMITER '';'' CSV', path);
  COST 100;
ALTER FUNCTION upsert_realtime(text, regclass)
  OWNER TO postgres;

the path supplied to the SP is on local machine:

select * from upsert_realtime('/home/.../travel_time.txt','realtime_travel_time');

Therefore I get this error:

terminate called after throwing an instance of 'soci::postgresql_soci_error'
  what():  Cannot execute query. ERROR:  could not open file "/home/.../travel_time.txt" for reading: No such file or directory

The only thing that comes into my mind is to somehow automatically scp the file to the postgresql server and then run the SP, which is not the best idea. can you help me find a solution? thanks thanks

1/28/2015 3:43:16 AM

Accepted Answer

You're using the wrong path. This is a common source of errors. It's all about "from the viewpoint of the server."

COPY with a file name instructs the PostgreSQL server to directly read from or write to a file. The file must be accessible by the PostgreSQL user (the user ID the server runs as) and the name must be specified from the viewpoint of the server. When PROGRAM is specified, the server executes the given command and reads from the standard output of the program, or writes to the standard input of the program. The command must be specified from the viewpoint of the server, and be executable by the PostgreSQL user. When STDIN or STDOUT is specified, data is transmitted via the connection between the client and the server.

If you don't have a usable path, you need to

  • move the file to the server, and use the right path,
  • invoke a program on the server that can access your local file,
  • use psql to connect to the remote server and read the local file.
1/28/2015 4:04:40 AM

Related Questions

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow