Postgres Pro
Downloads
Home   >   mailing lists

Thread: [NOVICE] COPY from temp table to main table insted of INSERT INTO

[NOVICE] COPY from temp table to main table insted of INSERT INTO

From
Stephen Froehlich
Date:

I tend to do mass inserts to my database, but INSERT INTO is taking quite a while for 100k values.

 

What is the syntax for using the COPY command to copy a well formatted temp table to the “end” of the primary table?  I am having trouble understanding https://www.postgresql.org/docs/9.5/static/sql-copy.html.

 

Thanks,

Stephen

 

Re: [NOVICE] COPY from temp table to main table insted of INSERT INTO

From
"David G. Johnston"
Date:
On Tue, Jul 11, 2017 at 9:45 AM, Stephen Froehlich <s.froehlich@cablelabs.com> wrote:

I tend to do mass inserts to my database, but INSERT INTO is taking quite a while for 100k values.

 

What is the syntax for using the COPY command to copy a well formatted temp table to the “end” of the primary table?  I am having trouble understanding https://www.postgresql.org/docs/9.5/static/sql-copy.html.



​Tables don't have beginnings or ends.

Are you intending to use client software to access the source data or are you planning on putting the source data in a location where the server o/s user can see it?​

If you already have an actual temporary table inside the database you wouldn't use COPY.  COPY is intended to transfer data from/to an external file (including stdin/stdout).

Generally:

(in psql)
BEGIN;
CREATE TEMP TABLE tmptbl ( cols );
\copy tmptbl from '/tmp/file-to-load.csv' with ( ... )
INSERT INTO tbl SELECT * FROM tmptbl;
COMMIT;

\copy in psql constructs an appropriate "COPY" SQL command, executes it on the server, and then funnels the contents of "file" to the server.

David J.

Re: [NOVICE] COPY from temp table to main table insted of INSERT INTO

From
Stephen Froehlich
Date:

I have already copied the source data over from R in an unnormalized form and then am normalizing it in SQL (via a series of queries).

 

Thanks,

Stephen

 

From: David G. Johnston [mailto:david.g.johnston@gmail.com]
Sent: Tuesday, July 11, 2017 11:00 AM
To: Stephen Froehlich <s.froehlich@cablelabs.com>
Cc: pgsql-novice@postgresql.org
Subject: Re: [NOVICE] COPY from temp table to main table insted of INSERT INTO

 

On Tue, Jul 11, 2017 at 9:45 AM, Stephen Froehlich <s.froehlich@cablelabs.com> wrote:

I tend to do mass inserts to my database, but INSERT INTO is taking quite a while for 100k values.

 

What is the syntax for using the COPY command to copy a well formatted temp table to the “end” of the primary table?  I am having trouble understanding https://www.postgresql.org/docs/9.5/static/sql-copy.html.

 

 

​Tables don't have beginnings or ends.

 

Are you intending to use client software to access the source data or are you planning on putting the source data in a location where the server o/s user can see it?​

 

If you already have an actual temporary table inside the database you wouldn't use COPY.  COPY is intended to transfer data from/to an external file (including stdin/stdout).

 

Generally:

 

(in psql)

BEGIN;

CREATE TEMP TABLE tmptbl ( cols );

\copy tmptbl from '/tmp/file-to-load.csv' with ( ... )

INSERT INTO tbl SELECT * FROM tmptbl;

COMMIT;

 

\copy in psql constructs an appropriate "COPY" SQL command, executes it on the server, and then funnels the contents of "file" to the server.

 

David J.

 

Re: [NOVICE] COPY from temp table to main table insted of INSERT INTO

From
"David G. Johnston"
Date:
On Tue, Jul 11, 2017 at 10:04 AM, Stephen Froehlich <s.froehlich@cablelabs.com> wrote:

I have already copied the source data over from R in an unnormalized form and then am normalizing it in SQL (via a series of queries).

 


​Then you won't be using COPY

David J.​

Re: [NOVICE] COPY from temp table to main table insted of INSERT INTO

From
Andreas Kretschmer
Date:

Am 11.07.2017 um 19:04 schrieb Stephen Froehlich:
> I have already copied the source data over from R in an unnormalized
> form and then am normalizing it in SQL (via a series of queries).

in this case you can use insert into ... select ... from ...

Regards, Andreas

--
2ndQuadrant - The PostgreSQL Support Company.
www.2ndQuadrant.com

Re: [NOVICE] COPY from temp table to main table insted of INSERT INTO

From
Stephen Froehlich
Date:
Will it be any faster if I put the INSERT INTO inside of a BEGIN INSERT INTO ... COMMIT?

-----Original Message-----
From: pgsql-novice-owner@postgresql.org [mailto:pgsql-novice-owner@postgresql.org] On Behalf Of Andreas Kretschmer
Sent: Tuesday, July 11, 2017 12:19 PM
To: pgsql-novice@postgresql.org
Subject: Re: [NOVICE] COPY from temp table to main table insted of INSERT INTO



Am 11.07.2017 um 19:04 schrieb Stephen Froehlich:
> I have already copied the source data over from R in an unnormalized 
> form and then am normalizing it in SQL (via a series of queries).

in this case you can use insert into ... select ... from ...

Regards, Andreas

--
2ndQuadrant - The PostgreSQL Support Company.
www.2ndQuadrant.com



-- 
Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-novice

Re: [NOVICE] COPY from temp table to main table insted of INSERT INTO

From
"David G. Johnston"
Date:
On Tuesday, July 11, 2017, Stephen Froehlich <s.froehlich@cablelabs.com> wrote:
Will it be any faster if I put the INSERT INTO inside of a BEGIN INSERT INTO ... COMMIT?

Assuming your original insert statement is executed in auto-commit mode, and you only have the one statement, then adding an explicit begin commit will not change anything since there is already an implicit transaction that gets run.

Note the assumptions.  It would help if you can give more actual code/details so those helping don't have to make as many.

David J.