I'm pulling in a csv file nightly and need to pump in into my db. My plan is to pump it into a temp table and then to an update or insert from the temp table to the real table. I'm having an issue, however, with the copy.
Here is a my syntax.
COPY cars FROM 'cars04.txt' USING DELIMITERS ',' WITH NULL AS '\null';
I'm getting there error: must be superuser to COPY to or from a file HINT: Anyone can COPY to stdout or from stdin. psql's \copy command also works for anyone. [4]
Ok, fair enough, in theory, I don't want to simply trust the file to have clean data so will want to put the data into a huge array (php) which I can clean and then pump into the db. What is the best way to pump all this data in without doing inserts? How can I use stdin?