Load DWH fails with Error: "value too long for type character varying(21)" in Postgres

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.

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.

Environment

Release : Any

Component :  CLARITY DATA WAREHOUSE

Database: Postgres

Resolution

1. Identify the mismatched field with query:
   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)"
2. Log in to Clarity
3. Exclude the field from Data Warehouse
4. Run a Full Load DWH job
5. Now go back and enable the field
6. Run a new Full Load DWH job

Additional Information

See also: Load DWH job frequently reported issues

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'