CODEWITHSUNDEEP
postgresqlazure-data-factoryjsonb
Mark Wojciechowicz
Mark Wojciechowicz

Reputation: 4477

ADF mapping data flow postgres error column is of type jsonb

I am using a mapping data flow in azure data factory to copy data between two postgres databases. The table has a column with a datatype of jsonb. It looks something like this:

Create table mytable 
(
    id int,
    json_column jsonb
);

The schema is the same in both databases. The error message kept getting cut off in ADF, so in my process of debugging I limited it to a single row, which happened to have NULL for the json column. The insert statement that ADF created was like this:

INSERT INTO "T_6726_7518bded039e4697aedcfafdd213e402" ("id","json_column","r0c0604bf127847bdac0e8012a40d8651") 
VALUES (123,NULL,8)

This returns the error:

ERROR: column "json_column" is of type jsonb but expression is of type character Hint: You will need to rewrite or cast the expression.

The same statement runs fine in pg_admin. Is there a work around for this? Is there actually no support for jsonb columns?

Upvotes: 0

Views: 168

Answers (1)

Bhavani
Bhavani

Reputation: 5317

When I tried to copy data from a Postgres SQL table with null values in the name column to another table using mapped data flow, I got the same error as shown below:

enter image description here

This may be a bug in data flow. I have tried the following workaround:

I added a derived transformation to the source and modified the column with null values using the expression below:

iif(isNull(<columnName>), '{"value": null}', <columnName>)

enter image description here

Data preview of the derived column transformation:

enter image description here

I added the following script as a post-copy script to the sink in the sink settings tab:

update <tableName> set name = NULL where <columnName> = '{
  "value": null
}'

enter image description here

When I ran the data flow activity with the reference of the data flow below, the table data copied successfully as shown:

enter image description here

Upvotes: 1

Related Questions