1

I'm merging two databases. The newer one has a table with an integer column failed_port which is NOT NULL. The older database does not have this column.

So after using pg_dump to dump both databases and then pg_insert to merge them there is an error:

pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 2336; 0 58976 TABLE DATA c_a postgres
pg_restore: [archiver (db)] could not execute query: ERROR:  null value in column "faild_port" violates not-null constraint
DETAIL:  Failing row contains (1, A, B, 2000, null, 2019-01-19 16:27:15.335126, Tool).
    Command was: INSERT INTO c_a (c_a_pk, c_b_fk, name, port, row_created, row_creator) VALUES (1...

The table in older database looks like this:

c_a_pk | c_b_fk | name | port  |        row_created         | row_creator 
-------+--------+------+-------+----------------------------+-------------
     1 | A      | B    |  2000 | 2019-01-19 16:27:15.335126 | Tool 

And in the newer like this:

c_a_pk | c_b_fk | name | port | failed_port |        row_created         | row_creator 
-------+--------+------+------+-------------+----------------------------+-------------
     1 | C      | D    | 2000 |           0 |2019-02-19 16:27:15.335126  | Tool 

How to merge the older table with the newer? Is there for example a command for pg_restore to automatically set default value to 0 if there is a NOT NULL column that doesn't exist in first table? I could do this manually, but I'm planning to merge multiple databases so it would be burdensome to do this by hand.

  • I'd first load with COPY to a new table and then use SQL to modify the existing table. COPY is not very smart (but fast). Use an unlogged table for speed! – Laurenz Albe May 17 at 8:47
1

Temporarily put a trigger on your target table to set default values:

CREATE FUNCTION override_null()
RETURNS trigger AS $$
BEGIN
  IF NEW.failed_port IS NULL THEN
    NEW.failed_port := 0;
  END IF;
  RETURN NEW;
END
$$ LANGUAGE 'plpgsql';

CREATE TRIGGER override_null_trigger
BEFORE INSERT ON c_a
FOR EACH ROW
EXECUTE PROCEDURE override_null();
  • Thank you, this worked. I don't think there is an easy way to automatically get the name of the column that throws the error, so that the function could be created for every not-null column. But luckily the column is the same in all the databases so only one function should suffice. – Asgmch May 17 at 11:10
  • More than welcome – Philᵀᴹ May 17 at 11:18
  • I noticed the trigger method doesn't work because I'm dumping and restoring with --disable-triggers. Any idea for a workaround on this @Philᵀᴹ ? – Asgmch May 17 at 13:47
  • Temporarily disable the triggers one-by-one with ALTER TABLE table_name DISABLE TRIGGER trigger_name, and leave the override ones active – Philᵀᴹ May 17 at 14:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.