10

Having a beast of a time trying to find a solution to trying to import a CSV (local on my machine - don't ask - this is a customer issue and it's the only way I can get data from them at the moment) into my pg database up on heroku. Have tried using COPY to read the file directly from a publicly accessible URL, but I need superuser rights in pg to be able to do that. Tried using cat to pipe the output of the file into stdin, but not sure if I've got the format of that correct (it always seems to complete but with no data imported - says "COPY 0" when it's done).

cat ~/Downloads/localfile.csv | heroku pg:psql -c "COPY testonly FROM STDIN WITH (FORMAT CSV);"

As I'm going to have to do this a few more times over the next couple of months, I'd rather not have to import this locally and then export a backup. Would be great if I could get the COPY option working.

Improve this question

1 Answer 1

Reset to default
15

You are close, except you need to feed your csv to your local psql. heroku pg:psql does not accept stdin.

Like this

cat ~/Downloads/localfile.csv | \
psql `heroku config:get DATABASE_URL` -c "COPY testonly FROM STDIN WITH (FORMAT CSV);"
Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

Beautiful! Worked like a champ.
Thanks @Stan. In my case, i also had to add --app <app_name> after DATABASE_URL :)
Sure. Or you could export HEROKU_APP_NAME=... so you don't have to specify it all the time. And you can use direnv.net to automatically manage it on a per-directory basis.
I'm getting an error psql: error: could not connect to server: FATAL: role "matthew" does not exist

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.