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?

Neil McGuigan · Accepted Answer · 2022-10-16 22:34:48Z

53

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

edited Oct 16, 2022 at 22:34

answered Oct 26, 2015 at 19:53

Neil McGuigan

48.5k12 gold badges130 silver badges156 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

RBB Over a year ago

I tried above yours and Schwern's. it is working fine (from console). but, while I trying this from Java code, getting "\" syntax error.

Schwern Over a year ago

Note that \copy is a psql command. It will not work in other contexts.

Abe Petrillo Over a year ago

Just in case it helps another person. If you get "missing data for column... it's because you forgot to specify the delimiter.

g0x11 Over a year ago

Great Work...:-)

mehmet Over a year ago

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

Schwern · Accepted Answer · 2015-10-26 20:02:36Z

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.

edited Oct 26, 2015 at 20:02

answered Oct 26, 2015 at 19:32

Schwern

167k28 gold badges225 silver badges370 bronze badges

Comments

groenhen · Accepted Answer · 2019-12-31 10:48:00Z

-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

edited Dec 31, 2019 at 10:48

groenhen

3,02725 gold badges51 silver badges69 bronze badges

answered Dec 31, 2019 at 10:22

Mohit Singh

334 bronze badges

Collectives™ on Stack Overflow

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

3 Answers 3

5 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

5 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related