The basic problem can be solved with various simple queries. Considering all columns:
CREATE TABLE tbl3 AS TABLE tbl1 UNION TABLE tbl2;
Given this additional information:
All columns except the
idcolumn should be considered for the unique check.
I don't need to preserve the ID column.
Just drop the
id column, then you can proceed with the simple query above.
I would import to temporary tables (much faster, less overhead) and only write the final result (
tbl3) to a regular table - in one session because temporary tables are dropped automatically at the end of the session.
CREATE TEMP TABLE tbl1 ( <columns from above, without id> ); COPY tbl1 FROM '/path/to/file1'; CREATE TEMP TABLE tbl2 ( <columns from above, without id> ); COPY tbl2 FROM '/path/to/file2';
Alternatively, to preserve the input tables across sessions, you could use unlogged tables.
For best performance create and fill the target with
CREATE TABLE AS and add the PK constraint in the same transaction:
BEGIN; CREATE SEQUENCE tbl3_tbl3_id_seq; CREATE TABLE tbl3 AS SELECT nextval('tbl3_tbl3_id_seq'::regclass)::int AS tbl3_id, * FROM (TABLE tbl1 UNION TABLE tbl2 ) sub; ALTER TABLE tbl3 ADD CONSTRAINT tbl3_pkey PRIMARY KEY(tbl3_id) , ALTER COLUMN tbl3_id SET DEFAULT nextval('tbl3_tbl3_id_seq'::regclass); ALTER SEQUENCE tbl3_tbl3_id_seq OWNED BY tbl3.tbl3_id; COMMIT;
Replace all occurrences of "tbl3" with our desired table name.
Detailed explanation in this related answer:
- http://dba.stackexchange.com/questions/114856/what-causes-large-insert-to-slow-down-and-disk-usage-to-explode/114926#114926What causes large INSERT to slow down and disk usage to explode?
I added a
serial column (
tbl3_id) as surrogate PK to the target table. Adding the actual PK constraint at the end (of the same session) is the fastest way.
- http://dba.stackexchange.com/questions/41059/optimizing-bulk-update-performance-in-postgresql/41111#41111Optimizing bulk update performance in PostgreSQL
Before you do it, test whether
double precision is the best data type for all those columns. Chances are, some of them could be
integer (cheaper for whole numbers) or must really be
numeric (loss-less). If so, adapt your temp tables to begin with.