COPY to remote databse from a local file

Question

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)
  RETURNS void AS
$BODY$
BEGIN   
    EXECUTE format('COPY temp_table(link_id,start_time,end_time,travel_time,interval_time, travel_mode) FROM %L DELIMITER '';'' CSV', path);
    --...     
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  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

score 2 · Accepted Answer · answered Jan 28 '15 at 04:04

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.

COPY to remote databse from a local file

1 Answers1