Pivotal Knowledge Base

Follow

gptransfer Error "null value in column "XXXXXXXX" violates not-null constraint"

Environment

Pivotal Greenplum Database (GPDB)

Symptom

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'

Resolution

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