Hi Stephen,
>>We have millions of record and would like to insert into a table. I
>>remebered people mentioned that "COPY" is the most effecient way to
>>insert data, right? If not, which is it, pg_restore?
>>
>>By the way, does it have to be superuser to run copy to and from?
>>
>>
>
>COPY is what you want. It doesn't have to be done as superuser if it's
>being sent over an existing connection to the database. The way to do
>this would be something like:
>
>zcat $file | psql -d db -h host -c "COPY $TABLE FROM STDIN;"
>
>
through command line "... copy ... stdin" works fine for me.
However, running "psql -d db -h ... from STDID", I believe we are
forced to type the password through prompt command line. Since our data
population task is through cronjob, is there a way, we can run "COPY ...
STDIN" by explicitly specifying password so that no human intervention?
>Probably the easiest to do would be to jump into psql and do '\h copy'.
>
Superuser's privileges is required under "psql>".
>Note that psql also has a '\copy' command which allows the same syntax but you can specify a file relative to the psql
>client. COPY $TABLE FROM 'file' requires superuser privileges and the file be on the server and the path to 'file' be
relativeto the server process. That would technically be a bit faster as the data wouldn't have to go across a socket
butrequires superuser and the file be on the server already...
>
>
Thanks a lot,
Emi