Pivotal Knowledge Base

Follow

gptransfer giving error 'null value in column "XXXXXXXX" violates not-null constraint'

Problem

When running gptranfer to migrate data from one DB to another it is failing with the error message:

20150526:16:02:28:623478 gptransfer:idb90:gpadmin-[ERROR]:-error 'ERROR:  canceling statement due to user request
' in 'INSERT INTO gptransfer.w_ext_prodbaltb_test_3b97830ce2dbc88920a2d2939313cca0 SELECT * FROM wise_fact.prodbaltb_test'
20150526:16:02:28:623478 gptransfer:idb90:gpadmin-[ERROR]:-error 'ERROR:  null value in column "commods_strategy" violates not-null constraint  (seg35 idb28.devin3.ms.com:16729pid=18955)
' in 'INSERT INTO wise_fact.prodbaltb_test SELECT * FROM gptransfer.ext_prodbaltb_test_3b97830ce2dbc88920a2d2939313cca0'

Solution

There are a few options as a workaround for this issue

1. Temporarily remove the constraint from the source table which the data is being transferred and then add back the constraint on the tables after the transfer is completed:

ALTER TABLE table_name ALTER COLUMN column_name DROP NOT NULL;

2. Add a default value for the column in the table:

ALTER TABLE table_name ALTER COLUMN column_name SET DEFAULT 99.99;

Comments

Powered by Zendesk