How can I troubleshoot "ERROR: null value in column violates not-null constraint" for my AWS DMS full load and CDC task?

Resolution

If you have large object (LOB) columns in your migration task, then you see a log entry similar to the following:

"E: Command failed to load data with exit error code 1, Command output: ERROR: null value in column "xyz" violates not-null constraint"

When AWS DMS migrates LOB columns, all data except for the LOB column are migrated into your target table. AWS DMS also inserts a NULL record in the LOB column. Then, AWS DMS updates the rows in the target table with the LOB column data.

If AWS DMS didn't create the target, then check the target data description language (DDL) to see if a NOT NULL attribute is specified. If there's a NOT NULL attribute, then update the table to remove the NOT NULL constraint on LOB column data types. Then, resume your AWS DMS task.

See the following table for AWS DMS task behavior for LOB modes and task phases:

Related Information

Migrating large binary objects (LOBs)