How do I resolve the null value error for my AWS DMS full load and CDC task with LOB column?

Short description

If you use AWS DMS to migrate data with a LOB column that has a NOT NULL constraint, then you might receive the following error message:

"ERROR: null value in column violates not-null constraint"

This error occurs when AWS DMS inserts NULL values in columns. In full LOB mode, AWS DMS migrates your column data, except for LOB columns, to your target table. AWS DMS sets the LOB columns to NULL. Then, AWS DMS uses a lookup command to populate the LOB data based on the primary key or unique key. If a LOB column has a NOT NULL constraint and AWS DMS tries to insert NULL into that column, then the insert fails.

To resolve this error, take one of the following actions:

• Modify your table structure so that the table doesn't use LOB columns with a NOT NULL constraint.
• Update your task to use limited LOB mode.

Resolution

Modify your table structure

To identify LOB columns, check the data definition language (DDL) of both your source and target tables.

To identify the NOT NULL constraint name on a PostgreSQL table, run one of the following queries:

Find NOT NULL columns

SELECT column_name FROM information_schema.columns WHERE table_name = 'documents' AND is_nullable = 'NO';

Drop NOT NULL for specific columns (e.g., "content")

ALTER TABLE documents ALTER COLUMN content DROP NOT NULL;

Note: Replace documents with your table name and content with the the column name returned from the previous query.

To modify your table structure, remove the NOT NULL constraint from the LOB column. Run the following command:

ALTER TABLE flng_meta DROP CONSTRAINT flng_meta_pkey;

Note: Replace flng_meta with your table name and flng_meta_pkey with your NOT NULL constraint name.

Use limited LOB mode

To modify your task to use limited LOB mode, complete the following steps:

1. Open the AWS DMS console.
2. In the navigation pane, choose Database migration tasks.
3. Select your migration task, and then choose Modify.
4. Under Task settings, for LOB column settings choose Limited LOB mode.
5. Set the Max LOB size value. The default is 32 KB.
6. Choose Save.
7. If the task is running, then stop the task.
8. Restart the task.

-or-

Use the JSON editor to modify your task settings.

1. Open the AWS DMS console.
2. In the navigation pane, choose Database migration tasks.
3. Select your migration task, and then choose Modify.
4. Under Task settings, choose JSON editor.
5. In the TargetMetadata section, modify the task settings.