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 "value too long for type character varying"

book

Article ID: 208268

calendar_today

Updated On:

Products

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

Issue/Introduction

On 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

Cause

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

Environment

Release : Any

Component : Data Warehouse (DWH)

Resolution

To fix the issue:

  1. From the query in the bg-dwh.log, identify the query that is run
  2. Run the queries on the database to identify the problem field
  3. Remove the field from the Data Warehouse
  4. Run a Full Load of the job
  5. Now add the field back to the Data Warehouse
  6. Run a Full Load of the job

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

To prevent the issue:

It's suggested that when the attribute size is changed:

  1. Exclude the attribute from the data warehouse
  2. Run the full load
  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: