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.
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);"