search cancel

Load Data Warehouse job fails with "value too long for type character varying(90)" error

book

Article ID: 229876

calendar_today

Updated On:

Products

Clarity PPM On Premise Clarity PPM SaaS

Issue/Introduction

Load Data Warehouse incremental job is failing, even when the full load is run. The error noticed in the bg-dwh.log is as below.

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

Environment

Version: 16.0.0

Database: Postgres

Resolution

1. Find all columns that are defined as varchar(90) in the DWH schema against DWH_INV_IDEA table using the query below

select *
from information_schema.columns
where lower(table_name) = 'dwh_inv_idea'

2. Find the source column on the PPM side using DWH_META_COLUMNS table

3. Find data that is more than 90 characters long using a query such as the one below

select investment_key, some_varchar_column
from dwh_idea_v
where length(some_varchar_column) > 90

4. Act upon the data. Analyze why this mismatch happened.