Postgresql : append the content of a table to another table

Question

i'd like to merge two tables with the same columns in postgresql. In fact, I already have one table in use and would like to append the content of a dump from another server to merge the tables. Both tables have the same columns including the primary key one... So when I try to load the dump I get duplicate primary key errors. I tried to load the dump into a separate table and insert the content of this new table into the first one with an INSERT/SELECT, but the primary key field generates errors because of the duplicates, and I can't omit it in the insert (or get a "not-null constraint" violation).

What would be the syntax (or solution) to append the content of the second table (or its dump) at the end of the first one, with correct primary keys?

In short, with an example, I would like to:

#
# First table: 
#
CREATE TABLE t_table1 (
    f_fullcmd text,
    f_id integer NOT NULL,
    f_body text
);

INSERT INTO t_table1 VALUES ('command1', 1, 'This is my first command');
INSERT INTO t_table1 VALUES ('command2', 2, 'This is my second command');
INSERT INTO t_table1 VALUES ('command3', 3, 'This is my third command');

ALTER TABLE ONLY t_table1 ADD CONSTRAINT pk_1_t_table1 PRIMARY KEY (f_id);

#
# Second table to append to t_table1
#
CREATE TABLE t_table2 (
    f_fullcmd text,
    f_id integer NOT NULL,
    f_body text
);

INSERT INTO t_table2 VALUES ('run-1', 1, 'This is my first run');
INSERT INTO t_table2 VALUES ('run-2', 2, 'This is my second run');
INSERT INTO t_table2 VALUES ('run-3', 3, 'This is my third run');

ALTER TABLE ONLY t_table2 ADD CONSTRAINT pk_1_t_table2 PRIMARY KEY (f_id);

#
# Resulting table:
#
CREATE TABLE t_merge (
    f_fullcmd text,
    f_id integer NOT NULL,
    f_body text
);

INSERT INTO t_merge VALUES ('command1', 1, 'This is my first command');
INSERT INTO t_merge VALUES ('command2', 2, 'This is my second command');
INSERT INTO t_merge VALUES ('command3', 3, 'This is my third command');
INSERT INTO t_merge VALUES ('run-1', 4, 'This is my first run');
INSERT INTO t_merge VALUES ('run-2', 5, 'This is my second run');
INSERT INTO t_merge VALUES ('run-3', 6, 'This is my third run');

ALTER TABLE ONLY t_merge ADD CONSTRAINT pk_1_t_merge PRIMARY KEY (f_id);

The inputs are the dumps of t_table1 and t_table2, and would like to get t_merge.

Answer 1

If both table are autonumeric already, instead of create a temporal table is simpler do this:

INSERT INTO t_table1 (f_fullcmd, f_id, f_body) 
SELECT f_fullcmd, 
       f_id + x.t1_max, 
       f_body 
FROM t_table2
CROSS JOIN (SELECT MAX(f_id) t1_max FROM t_table1) x ;

Answer 2

OK, so after some trials and research, it seems the sequence to do this is:

--
-- First table: 
--
CREATE TABLE t_table1 (
    f_fullcmd text,
    f_id integer NOT NULL,
    f_body text
);

INSERT INTO t_table1 VALUES ('command1', 1, 'This is my first command');
INSERT INTO t_table1 VALUES ('command2', 2, 'This is my second command');
INSERT INTO t_table1 VALUES ('command3', 3, 'This is my third command');

ALTER TABLE ONLY t_table1 ADD CONSTRAINT pk_1_t_table1 PRIMARY KEY (f_id);

--
-- Second table to append to t_table1
--
CREATE TABLE t_table2 (
    f_fullcmd text,
    f_id integer NOT NULL,
    f_body text
);

INSERT INTO t_table2 VALUES ('run-1', 1, 'This is my first run');
INSERT INTO t_table2 VALUES ('run-2', 2, 'This is my second run');
INSERT INTO t_table2 VALUES ('run-3', 3, 'This is my third run');

ALTER TABLE ONLY t_table2 ADD CONSTRAINT pk_1_t_table2 PRIMARY KEY (f_id);

--
-- Create a temp table:
--
CREATE TABLE t_table3 (
    f_fullcmd text,
    f_id serial NOT NULL,
    f_body text
);

SELECT setval('t_table3_f_id_seq', (SELECT MAX(f_id)+1 FROM t_table1), false);

--
-- Merge table2 into table3
--
INSERT INTO t_table3 (f_fullcmd, f_body) SELECT f_fullcmd, f_body FROM t_table2;

--
-- Merge back into table1:
--
INSERT INTO t_table1 (f_fullcmd, f_id, f_body) SELECT f_fullcmd, f_id, f_body FROM t_table3;

When creating t_table3, change the primary key to be a sequence, then set its starting value to the max value of the first table primary key +1, insert into t_table3 the content of t_table2 (os re-generating the primary key as a sequence), and finally import back the content of t_table3 into t_table1.