Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Importing a CSV dump of Postgres data into Heroku
# You have your csv data and it looks like so... It's in a file named "my_data.csv" and we want to import it into a table named "my_things".
"1", "Something", "0.50", "2013-05-05 10:00:00"
"2", "Another thing", "1.50", "2013-06-05 10:30:00"
# Now you want to import it, go to the command line and type:
$ PGPASSWORD=PWHERE psql -h HOSTHERE -U USERHERE DBNAMEHERE -c "\copy my_things FROM 'my_data.csv' WITH CSV;"
# Voila! It's impoted. Now if you want to wipe it out and import a fresh one, you would do this:
$ heroku pg:psql
$ TRUNCATE table my_things;
Now re-do the PGPASSWORD command above:
$ PGPASSWORD=PWHERE psql -h HOSTHERE -U USERHERE DBNAMEHERE -c "\copy my_things FROM 'my_data.csv' WITH CSV;"
@kfrn

This comment has been minimized.

Copy link

kfrn commented Jan 23, 2017

Thank you for this :)

@radzia2

This comment has been minimized.

Copy link

radzia2 commented Aug 19, 2017

worked for dev-posgtres database, however, how would this work for a standard_0 database with ssl active? I get this error:

psql: FATAL: password authentication failed for user "my_user_name"
FATAL: no pg_hba.conf entry for host "my_ip_addres", user "my_user_name", database "my_database", SSL off

@radzia2

This comment has been minimized.

Copy link

radzia2 commented Aug 19, 2017

problem solved :-) i connected to the database

heroku pg:psql

changed to my_database

\c my_database

and then used following command:

\copy my_database_table FROM PROGRAM 'curl "url_where_the_cvs_is"' WITH CSV;

@picoeric

This comment has been minimized.

Copy link

picoeric commented Jul 19, 2018

Thanks!!

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.