Skip to content

Instantly share code, notes, and snippets.

@jboesch
Last active April 5, 2022 22:11
Show Gist options
  • Save jboesch/5605747 to your computer and use it in GitHub Desktop.
Save jboesch/5605747 to your computer and use it in GitHub Desktop.
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;"
@radzia2
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
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
Copy link

Thanks!!

@jdr81394
Copy link

jdr81394 commented Jan 6, 2020

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

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment