How to insert CSV data into PostgreSQL database (remote database )

Question

I have a CSV file. I can, using the command below, insert the data into a database if file is on the same server as the database:

psql -h localhost -d local_mydb -U myuser -c "copy mytable (column1, column2)  from '/path/to/local/file.csv' with delimiter as ','"

But the file is on a local server and the database is on another (remote) server.

If I try to do this for a remote server, using the command below:

psql -h remotehost -d remote_mydb -U myuser -c "copy mytable (column1, column2)  from '/path/to/local/file.csv' with delimiter as ','"

I get a permission-denied exception.

How can I insert data from a local file into a database on a remote server, without superuser privileges?

Answer 1

\copy (note the backslash) lets you copy to/from remote databases and does not require superuser privileges.

psql -h remotehost -d remote_mydb -U myuser -c "\copy mytable (column1, column2)  from '/path/to/local/file.csv' with delimiter as ','"

If you're using Java, you can use the CopyManager in the JDBC driver: https://jdbc.postgresql.org/documentation/publicapi/org/postgresql/copy/CopyManager.html

Answer 2

You can feed the file via STDIN. From the PostgreSQL COPY documentation...

When STDIN or STDOUT is specified, data is transmitted via the connection between the client and the server.

psql -h remotehost -d remote_mydb -U myuser -c \
    "copy mytable (column1, column2) from STDIN with delimiter as ','" \
    < /path/to/local/file.csv

---

I was incorrect about using FROM PROGRAM. It has the same caveats as FROM 'filename'. The server executes the program, not the client.

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.