Some PostgreSQL Numeric Columns Are Replicated as STRING Columns in BigQuery

Issue

Some PostgreSQL numeric columns replicated as STRING columns in BigQuery.

Environment

• Connector: PostgreSQL
• Destination: BigQuery

Resolution

To resolve this issue:

1. In PostgreSQL, alter the affected numeric column to use explicit precision and scale. For example, change the column type to numeric(18,6).
2. In Fivetran, go to your PostgreSQL connection page.
3. Select the Schema tab.
4. Locate and hover over the affected table.
5. On the right side of the screen, click Resync.
6. In the confirmation pop-up window, click Start re-sync.

Cause

This issue occurs when the PostgreSQL numeric columns are created without an explicit scale and precision. When this happens, the columns allow arbitrary size and precision, which may exceed the BigQuery NUMERIC limits of 38 decimal digits and 9 decimal places.

To prevent data loss, Fivetran replicates PostgreSQL numeric columns without explicit precision or scale as STRING columns in BigQuery.