Thread: Question about COPY to/from

Hello,

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?

Thanks a lot,
Emi

* Emi Lu (emilu@encs.concordia.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

On Mon, 2006-02-20 at 16:39, Emi Lu wrote:
> Hello,
>
> 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?

Take a look inside a pg_dump output and guess what you'll find... Lots
and lots of copy commands.



>
> By the way, does it have to be superuser to run copy to and from?

Take a look in a pg_dump file again.  You'll see constructs something
like this:

COPY config (conf_name, conf_value) FROM stdin;
o_cur_version   1.2.5
o_board_title   My PunBB forum
o_board_desc    Unfortunately no one can be told what PunBB is - you
have to see it for yourself.
o_server_timezone       0
o_time_format   H:i:s
\.

So, no.  You don't have to be a super user to use COPY, as long as
you're reading from STDIN and not a file.

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

Emi Lu <emilu@encs.concordia.ca> writes:
> 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?

Best way is to put the password in ~/.pgpass file belonging to the
account that runs the cron job.

            regards, tom lane