How to resolve data mapping between parquet file and Azure Database for PostgreSQL ?

Question

I'm trying to copy data into a PostgreSQL database from a parquet file in an Azure Data Lake. The problem is that some of the numeric columns in the parquet file don't have exactly the same type of numeric data in the PG database.

longitude, latitude and indice_confiance raise a 2200 with this error message :
'Type=Npgsql.PostgresException,Message=22P02: invalid input syntax for type double precision

I've already tried changing the data type for the parquet file several times (double, float, etc) but it didn't change anything.

Answer 1

Hi @sliemsoonuef

Welcome to the Microsoft Q&A and thank you for posting your questions here.

You are trying to resolve an error "Message=22P02: invalid input syntax for type double precision." You have also tried changing the data type for the parquet file several times (double, float, etc) but it didn't change anything.

Yes, there is mismatch between the data types expected by PostgreSQL and the data types present in your Parquet file. My best advice since the data types are not directly compatible, you might need to perform data conversion during the copy operation. This can be done using Azure Data Factory's Data Flow feature or by preprocessing the data before loading it into PostgreSQL.

I hope this is helpful! Do not hesitate to let me know if you have any other questions.

Please remember to "Accept Answer" if answer helped, so that others in the community facing similar issues can easily find the solution.

Best Regards.