Getting "error_code": 1207 while validating before loading Redshift database

Hi @amardeep2021

You still have this issue, but I now can’t reproduce the problem against a real or double field! I may have misspoken and have since tore down the pipeline that I’m sure was getting a 1207 validation error for a number with a trailing space trying to insert into a real field. Let’s back up.

I’ve attached a pipeline with just two snaps, a JSON Generate and Redshift BulkLoad.

Here’s the payload for the generator; notice trailing space in number value.

[
    {
        "id": 1,
        "bigdecimal": "595546349.04 "
    }
]

Let’s create the Redshift table to bulk load into. Observe number data type is an integer, so we expect problems.

CREATE TABLE t1207 (
    id         int,
    bigdecimal integer
);

Sure enough, the BulkLoad snap reports this error. We expects this.

[
  {
    "error": "Invalid digit, Value '.', Pos 9, Type: Integer",
    "status_code": "error",
    "line_number": 1,
    "column_name": "bigdecimal",
    "error_code": 1207,
    "column_value": "595546349.04",
    ...
  }
]

The type in the error message, Integer, indicates the field we’re trying to insert into. Ok, let’s drop table and re-create with real data type.

CREATE TABLE t1207 (
    id         int,
    bigdecimal real
);

Re-running the pipeline succeeds without having to trim the number value, which might make better sense, because you’re still having a problem after doing this indicating problem is something else.

[
  {
    "id": 1,
    "bigdecimal": 5.9554637E+8
  }
]

However, we have lost some precision. This is a big number. So, let’s drop table and re-create as double precision which is probably what we want.

CREATE TABLE t1207 (
    id         int,
    bigdecimal double precision
);

Pipeline succeeds and full precision is preserved.

[
  {
    "id": 1,
    "bigdecimal": 595546349.04
  }
]

I tried changing JDBC drivers from Postgres to Redshift and get same result with either. I thought maybe Redshift driver was handling space trimming, but apparently this isn’t a problem with either.

As I mentioned, BulkLoad first writes data to a CSV file, zips it, and then stores this in S3 for Redshift to source from. My Redshift account and BulkLoad snap don’t have any special properties defined.

Maybe, you can share a bit more about your configuration, settings, or anything else suspect.

redshift-bulkload-1207-error_2021_10_27.slp (5.6 KB)