Importing a CSV dump of Postgres data into Heroku

pg_import_csv_to_heroku.sh

# 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;"

Thank you for this :)

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

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;

Thanks!!

I am trying to do this but it gives me the error: ERROR: must be superuser or a member of the pg_read_server_files role to COPY from a file
HINT: Anyone can COPY to stdout or from stdin. psql's \copy command also works for anyone.

Can anyone help?

Best
Jake