Remote CSV import Postgres

Question

What is the best way to automatically import CSVs into a remote Postgres server?

I am using a temperature sensor connected to my raspberry Pi to log data (using a python script) into a CSV file:

2013-11-11 23:00:25,27.187
2013-11-11 23:00:31,27.25
2013-11-11 23:00:36,27.25
2013-11-11 23:00:41,27.5

Using the COPY command in psql, I have no problems importing into a table if the CSV file is on the machine running postgres.

How can I import the data directly into a table on my postgres server from my Raspberry Pi?

(In the future, I would like to have my Pi import data autonomously, updating the current table by adding new rows as the Pi logs more data)

The simplest thing would be to have a script that first scp's the file from the Pi and then runs psql -c "Copy .." — Owen
– Owen, Commented Nov 12, 2013 at 0:38
possible duplicate of Recreating Postgres COPY directly in Python? — Daniel Vérité
– Daniel Vérité, Commented Nov 12, 2013 at 16:04

Peter Eisentraut · Accepted Answer · 2013-11-12 15:56:18Z

1

As you say, the COPY command works with a file residing on the database server host. Instead, you should use the \copy command in psql, which reads the file from the client host. See the man page of psql for details.

answered Nov 12, 2013 at 15:56

Peter Eisentraut

37.1k12 gold badges91 silver badges91 bronze badges

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

Comments

Arun Taylor · Accepted Answer · 2013-11-12 00:46:58Z

0

Assuming you have network connectivity, you should be able to make a socket connection to PostgreSQL and insert rows into a table as sensor data is collected.

The Connection Settings will need to have listen_addresses set up so that remote clients can connect with it.

answered Nov 12, 2013 at 0:46

Arun Taylor

1,5728 silver badges5 bronze badges

1 Comment

kensw Over a year ago

Thank everyone you this worked perfectly for me. After I made a connection used the \copy command mentioned by @peter.

Collectives™ on Stack Overflow

Remote CSV import Postgres

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related