ALERT: Some images may not load properly within the Knowledge Base Article. If you see a broken image, please right-click and select 'Open image in a new tab'. We apologize for this inconvenience.

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

book

Article ID: 199499

calendar_today

Updated On:

Products

Clarity PPM SaaS Clarity PPM On Premise STARTER PACK-CLARITY PPM

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'
  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

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'