31

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?

Peter Mortensen
  • 30,738
  • 21
  • 105
  • 131
Spike
  • 734
  • 1
  • 12
  • 22

3 Answers3

50

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

psql \
  -h $hostname -d $dbname -U $username \
  -c "\copy mytable (column1, column2)  from '/path/to/local/file.csv' with delimiter as ','"

https://www.postgresql.org/docs/current/sql-copy.html

Neil McGuigan
  • 46,580
  • 12
  • 123
  • 152
  • I tried above yours and Schwern's. it is working fine (from console). but, while I trying this from Java code, getting "\" syntax error. – Spike Oct 26 '15 at 19:59
  • 1
    Note that [`\copy` is a psql command](http://www.postgresql.org/docs/current/static/app-psql.html#APP-PSQL-META-COMMANDS-COPY). It will not work in other contexts. – Schwern Oct 26 '15 at 19:59
  • 1
    Just in case it helps another person. If you get "missing data for column... it's because you forgot to specify the delimiter. – Abe Petrillo Mar 27 '18 at 00:28
  • 1
    Great Work...:-) – Gowtham Balusamy Jul 25 '18 at 11:00
  • if you have comma in data (e.g., "123 main st, new york, usa") `with delimiter as ','` does not work.. replace it with `CSV` instead – mehmet Dec 13 '21 at 19:05
24

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.

Schwern
  • 153,029
  • 25
  • 195
  • 336
-1

For local database, you will simply use:

COPY TABLENAME FROM 'D:\xyz.csv' DELIMITER ',' CSV HEADER

For Server (remote database), you have to add \ :

\COPY TABLENAME FROM 'D:\xyz.csv' DELIMITER ',' CSV HEADER
סטנלי גרונן
  • 2,917
  • 23
  • 46
  • 68
Mohit Singh
  • 33
  • 4