* Emi Lu (emilu@xxxxxxxxxxxxxxxxx) wrote: > 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;" Depending on the input format you might add "WITH CSV" after 'STDIN', or if you want to limit it to a subset of columns you can say '$TABLE (a,b,c)'. Probably the easiest to do would be to jump into psql and do '\h copy'. 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... Enjoy, Stephen
Attachment:
signature.asc
Description: Digital signature
