Load DWH Error: "value too long for type character varying" - Identify the field failing

Products

• Clarity PPM SaaS
• Clarity PPM On Premise

Issue/Introduction

Load Data Warehouse (DWH) is failing with error:

ENCOUNTERED EXCEPTION WHILE MERGING INTO DWH_INV_INVESTMENT. value too long for type character varying(21)

Table and column length may vary.

 

How to identify which field is failing? 

Environment

Release : Any

Component :  CLARITY DATA WAREHOUSE

Database: Postgres

Cause

This is caused by a mismatched length for an attribute, likely if the length was changed but the attribute was already added to Data Warehouse. 

Resolution

1. Have you recently changed any attribute on this object by increasing its length in Clarity.
   • If yes, this is likely the field you need. It's required to uncheck and check the field back & Run Full Load when changing the attribute length
2. If you do not know, then identify the mismatched field with query on the DWH database:
   select table_name, column_name, data_type, character_maximum_length from information_schema.columns where table_name = 'dwh_inv_investment' and character_maximum_length >= xx 
   (replace 'xx' with the number inside the error, like 21 for "value too long for type character varying(21)"
3. The query will show you a number of fields. Compare the size to dwh_meta_columns length in Clarity database to identify the problem field
4. Depending on the error message, adjust the query accordingly, example:
   • Same error on the DWH_INV_TASK
   • ERROR: ENCOUNTERED EXCEPTION WHILE MERGING INTO DWH_INV_TASK. value too long for type character varying(255)
       Where: PL/pgSQL function inline_code_block line 22 at RAISE
   • Use following query to identify the attribute:
   • select table_name, column_name, data_type, character_maximum_length from information_schema.columns where table_name = 'dwh_inv_task'

Additional Information

To fix the field, use the steps below once identified:

1. 1. Log in to Clarity
   2. Exclude the field from Data Warehouse
   3. Run a Full Load DWH job
   4. Now go back and enable the field
   5. Run a new Full Load DWH job

Check KB Load DWH fails with "value too long for type character varying" for full information on cause and prevention