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 relative to the server process. That would technically be a bit faster as the data wouldn't have to go across a socket but requires superuser and the file be on the server already...
Thanks a lot,
Emi
