Load DWH fails with "value too long for type character varying"

Products

• Clarity PPM On Premise
• Clarity PPM SaaS

Issue/Introduction

When running Load Data Warehouse (DWH), an error is thrown:

ERROR: ENCOUNTERED EXCEPTION WHILE INSERTING INTO DWH_ODF_TEST. value too long for type character varying(60)
  Where: PL/pgSQL function inline_code_block line 23 at RAISE

Environment

Release : Any

Cause

This is an issue due to a change in attribute size length that wasn't reflected in the Data Warehouse. It may not be noticeable at first if the data in the field still falls within the original size of the attribute.

Resolution

To fix the issue:

1. From the query in the bg-dwh.log, identify the query that is run
2. Run the query on the database to identify the problem field as per Load DWH Error: "value too long for type character varying" - Identify the field failing
3. Exclude the field from the Data Warehouse and click Save.
4. Run a Full Load of the DWH job
5. Include the field back into to the Data Warehouse. Click Save.
6. Run a Full Load DWH job.

This should recreate the field in the Data Warehouse with the correct length.

To prevent the issue:

When the attribute size is changed:

1. Exclude the attribute from the Data Warehouse
2. Run the full Load DWH job
3. Include the attribute back in the Data Warehouse

This should ensure the size updates correctly before you run into any errors on the Load Data Warehouse job.

Additional Information

Reference also:

• Load DWH job failures - troubleshooting
• Data Warehouse (DWH) known issues
• Load DWH Full - When to run to prevent failures