Null Values in Azure Postgres Server via Azure ADF

Question

Hi team, I have created tables in Azure Postgres server database, with default value definition for integer, float like for integer default value as 0 and for float default value as 0.0. But when the data in csv/xslx is null it is getting copied as null only in the database table. The default value definition is not getting applied. Could you please suggest what can be done for this. Attached the example for this.

Answer 1

Hi @Dinesh Prajapati , Thanks for using Microsoft Q&A forum and posting your query. When you define a default value for a column in a PostgreSQL table, the default value is only used when a value is not provided for that column during an insert operation. For example, as below:

If a null value or an empty string value is explicitly provided for the column, the default value is not used. Below is a sample:

In your case, it sounds like the CSV/XLSX files that you are using to load data into the PostgreSQL table contain null values for some of the columns. When these null values are loaded into the table, they are overriding the default values that you have defined for those columns as shown in above example (2). Please correct if my understanding is wrong.

If that's the case, then looks like it is an expected behavior from Postgres SQL. But to work around this issue, you can modify your Azure Data Factory pipeline to replace null values in the CSV/XLSX files with the default values that you have defined for the corresponding columns using mapping data flow activity and derived column transformation.

You can do this using the "Derived Column" transformation in Azure Data Factory. Here's an example of how you can use the "Derived Column" transformation to replace null values with default values:

1. Add a "Derived Column" transformation to your pipeline.
2. In the "Derived Column" transformation, add a new column for each column in your PostgreSQL table that has a default value defined.
3. For each new column, use the following expression to replace null values with the default value: iif(isNull(columnName), defaultValue, columnName) Replace "columnName" with the name of the column in the CSV/XLSX file, and "defaultValue" with the default value that you have defined for the corresponding column in the PostgreSQL table.
4. Map the new columns to the corresponding columns in the PostgreSQL table.
5. Run the pipeline to load the data into the PostgreSQL table. With this approach, any null values in the CSV/XLSX files will be replaced with the default values that you have defined, ensuring that the default values are used when a value is not provided for a column during an insert operation. I'm also reaching out to internal team to check if there is any improvement, they can do on the ADF side so that we can avoid the additional transformation and will keep you posted as I hear back from the team. Hope this info helps. Please let us know if you have further query.

Please don’t forget to Accept Answer and Yes for "was this answer helpful" wherever the information provided helps you, this can be beneficial to other community members.