Issue with syncing numeric fields from Postgres source to Postgres destination

Slack Knowledge Base Q&A Connector Questions
, , , ,
1

Summary

Numeric fields from a Postgres source are being synced as NULL on the destination due to a destination typecast error. The issue occurs on subsequent incremental syncs but not on the initial sync. Types on source and destination are compatible.


Question

Hello,

I’m having an issue syncing some numeric fields form a Postgres source to a Postgres destination. The fields are being synced as NULL on the destination.

Reading the _airbyte_meta column, the problem is supposed to be some sort of type mismatch but it make little sense to me, since the type on the source is numeric(30, 5) and the type on the destination set by Airbyte is numeric(38, 9). The types are compatible (as far as I know)

{"changes": [{"field": "platformFee", "change": "NULLED", "reason": "DESTINATION_TYPECAST_ERROR"}, {"field": "baseCreditAmount", "change": "NULLED", "reason": "DESTINATION_TYPECAST_ERROR"}, {"field": "excessCreditAmount", "change": "NULLED", "reason": "DESTINATION_TYPECAST_ERROR"}]}
Interesting enough, Airbyte setting these fields to null only happens on subsequent incremental syncs. The initial sync has no problems, an the fields are not nulled.

The sync mode for the table with the problem is set as Incremental - Append + Deduped , and the source postgres DB update method is set to Read Changes using Write-Ahead Log (CDC)

Can anyone point on how to solve this issue?


This topic has been created from a Slack thread to give it more visibility.
It will be on Read-Only mode here. Click here if you want
to access the original thread.

Join the conversation on Slack

["syncing", "numeric-fields", "postgres-source", "postgres-destination", "type-mismatch", "incremental-sync", "typecast-error"]
2

I got the same issue. The error is more specific, because of the double quote ". I wonder who can help to solve this?

ERROR type-and-dedupe i.a.i.b.d.t.TypeAndDedupeTransaction(executeTypeAndDedupe):48 Encountered Exception on unsafe SQL for stream INV SOAllocation with suffix , attempting with error handling org.postgresql.util.PSQLException: ERROR: invalid input syntax for type numeric: ""1.00""
I go deep in db query, could see this:

	"_airbyte_data" -> 'Qty' is not null and JSONB_TYPEOF("_airbyte_data" -> 'Qty') <> 'null' 
	and "pg_temp"."airbyte_safe_cast"(
		case when (
			"_airbyte_data" -> 'Qty' is null or 
			JSONB_TYPEOF("_airbyte_data" -> 'Qty') = 'null') 
			then null 
			else cast("_airbyte_data" -> 'Qty' as varchar) 
		end, 
		cast(null as decimal(38, 9))
	) is null
	) 
THEN JSONB_BUILD_OBJECT('field', 'Qty', 'change', 'NULLED', 'reason', 'DESTINATION_TYPECAST_ERROR') 
ELSE cast(null as jsonb) 
END```
And YES, Interesting enough, Airbyte setting these fields to `null` only happens on subsequent incremental syncs.
3

We’re also facing the same issue. Have there been any fixes or workarounds found for this?