Re: Question about COPY to/from

* Emi Lu (emilu(at)encs(dot)concordia(dot)ca) 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