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