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
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.
