Thread: bulk copy

Hello all,

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?

Thanks!

On Tue, Jan 15, 2008 at 12:18:18PM -0500, blackwater dev wrote:
> 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?

As the message states, if you're using psql you use the \copy command
and it all works If you're working from PHP you say "COPY FROM STDIN
..." and then use the (checks online PHP docs) pg_put_line function.

There's even an example:
http://www.maconlinux.net/php-online-manual/en/function.pg-put-line.html

Have a nice day,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> Those who make peaceful revolution impossible will make violent revolution inevitable.
>  -- John F Kennedy