Skip to main content
  • EN

Hello,

I'm currently having a problem with my PostgreSQL and Postgis writers.

When the feature operation is set to "INSERT", the INSERT is perform on all columns of the table instead of just the columns that are specified in the attribute definition.

It result in empty data on columns that arn't defined in the feature attributes but have a default value in the DB.

 

For exemple :

I have a table with 4 columns (column_1, column_2, column_3, column_4).

The column_3 have a default value ('my_default')

 

In my script, I have a feature to INSERT some data into this table. This feature have an automatic attribute definition (to avoid defining every new column each time I modify my script).

But my customer havn't any data for the column_3 and the column_4.

So, the feature in FME have only column_1 and column_2 defined in it's attribute definition (beacause I chose the automatic definition option).

After I run the script, the result I'm expecting is this one :

column_1column_2column_3column_4data1.1data1.2my_defaultNULL

 

Instead of this, I have this result :

column_1column_2column_3column_4data1.1data1.2NULLNULL

 

It's beacause FME perform this request :

INSERT INTO my_schema.my_table (column_1, column_2, column_3, column_4)
VALUES ('data1.1', 'data1.2', NULL, NULL);

Instead of this one :

INSERT INTO my_schema.my_table (column_1, column_2)
VALUES ('data1.1', 'data1.2');

 

As it inserts NULL value for the column_3, the default value can not be used.

 

My question :

Is there any option to avoid this behaviour and keep my default value ?

 

PS : I've extremly simplified my case, as our data model is constantly evolving, it will be complicated to define the structure of every table in the attribute definition of FME. It's easier to INSERT only in column that have data and let others working with default values.

The automatic definition helps us as we just have to name a field with the correct name to have it inserted in the DB (we don't have to defined it into the attribute definition).

@savoierider

At writer level:

simplest way is to set the default value at the writer level. (if an entire column has to be set to same value)

Just right click on the attribute on the writer and use Edit Attribute. Enter default value.

 

Prior to writer:

Use a attribute creator, import the columns directly form the table.

Use a script to get al constraints. Get the default values and conditionally (is null or missing) set the attribute to it using a for instance a attribute creator.

@savoierider

At writer level:

simplest way is to set the default value at the writer level. (if an entire column has to be set to same value)

Just right click on the attribute on the writer and use Edit Attribute. Enter default value.

 

Prior to writer:

Use a attribute creator, import the columns directly form the table.

Use a script to get al constraints. Get the default values and conditionally (is null or missing) set the attribute to it using a for instance a attribute creator.

Hi @gio,

Thank's for your answer and your time. Unfortunatly that's not exactly what i'm searching.

 

Adding a default value at the writer level makes me define every column with a default value even if I have no data for it.

 

=> As my MCD is constantly evolving (not every day but every month), I would need to check all these fields every time I edit my script and add/remove fields with default.

 

=> To much work (very time consumming).

 

Using attribute creator : same as above (but that was my first attenpted to resolve this trouble).

 

Using a script : currently what I'm doing but in SQL directly after the writting : maybe the simplest way to maintain all these default values (but needs SQL skills to have something clean).

 

In fact, my script helps us to import data from our customers data models to our own data model. My goal is to create a script that can be use by people that don't have good skills in FME AND for every customers.

 

The script contain all you need in the writer so my collegues just have to import customers feature, make some simple transformations (renaming fields, changing some values not much more) and plug it into writer's features.

 

If some of FME developpers could add an option to INSERT only in defined columns that could be a really nice thing.

If you just have one attribute in the writer feature type and the writer is set to INSERT it will only insert into that one attribute.

If you just have one attribute in the writer feature type and the writer is set to INSERT it will only insert into that one attribute.

Thank's for the tip, unfortunatly I have up to 20 attributes to INSERT.

 

Hi @savoierider,

 

There are a couple of workarounds that might help while our team works on adding support for this (related to: FMEENGINE-8945).

 

 

Method 1:

  1. Use a Tester to find features that do not have a value for the column that has a default value ("C3" in this case).

     

     

     

  2. Write features that are missing values using an SQLExecutor using an SQL INSERT statement

     

     

    Notice: C3 (the column with a default value set) isn't included in the insert statement and the values are set by the attribute values of the current feature.

     

    By inserting values from an SQL statement, you are allowing the default value to be set by the database as this is the equivalent to executing the SQL statement directly in PGAdmin.

     

     

  3. Lastly, connect the Failed port of the Tester to a PostgreSQL writer. Since these features already have attribute values for the C3 attribute they can be sent to a writer.

     

This approach allows the database to set the default value based on the column properties in PGAdmin.

Method 2:

Use a transformer to set the default value and write features using a PostgreSQL writer. This could be done using a number of different transformers, but this example uses the NullAttributeMapper to set the default value before writing features with the PostgreSQL writer.

 

You could also use conditional statements in a transformer like the AttributeManager.

I have attached a sample workspace that demonstrates how this works. If you want to try running the workspace as is, first create a new table in PGAdmin in the public schema called "TestCreateColumns" with 4 columns:

Column NameTypeC1TextC2CharC3Double - set the default value on this column - I used 100C4Double

Next, run the workspace (set your database connection when prompted) with one of the bookmarks enabled and view the output in PGAdmin.

Template: Insert_Default_Values.fmwt

Hi @savoierider,

 

There are a couple of workarounds that might help while our team works on adding support for this (related to: FMEENGINE-8945).

 

 

Method 1:

  1. Use a Tester to find features that do not have a value for the column that has a default value ("C3" in this case).

     

     

     

  2. Write features that are missing values using an SQLExecutor using an SQL INSERT statement

     

     

    Notice: C3 (the column with a default value set) isn't included in the insert statement and the values are set by the attribute values of the current feature.

     

    By inserting values from an SQL statement, you are allowing the default value to be set by the database as this is the equivalent to executing the SQL statement directly in PGAdmin.

     

     

  3. Lastly, connect the Failed port of the Tester to a PostgreSQL writer. Since these features already have attribute values for the C3 attribute they can be sent to a writer.

     

This approach allows the database to set the default value based on the column properties in PGAdmin.

Method 2:

Use a transformer to set the default value and write features using a PostgreSQL writer. This could be done using a number of different transformers, but this example uses the NullAttributeMapper to set the default value before writing features with the PostgreSQL writer.

 

You could also use conditional statements in a transformer like the AttributeManager.

I have attached a sample workspace that demonstrates how this works. If you want to try running the workspace as is, first create a new table in PGAdmin in the public schema called "TestCreateColumns" with 4 columns:

Column NameTypeC1TextC2CharC3Double - set the default value on this column - I used 100C4Double

Next, run the workspace (set your database connection when prompted) with one of the bookmarks enabled and view the output in PGAdmin.

Template: Insert_Default_Values.fmwt

Hello,

Thank you for your answer and sorry for being late.

 

Your two methods seems interesting but doesn't answer my problem.

 

In fact, I need a solution where I didn't have to specify every fields that have no value.

I have hundreds of tables and thousands of fields. Our data model is constantly evolving and it's nearly impossible to check every fields everytime.

That's why I use dynamic writers. But, as normal writers do, they force NULL values in default columns.

For now I'm using a SQL script that does the job by replacing every NULL and empty values by the default values.

 

Have a nice day

Hi @savoierider,

 

There are a couple of workarounds that might help while our team works on adding support for this (related to: FMEENGINE-8945).

 

 

Method 1:

  1. Use a Tester to find features that do not have a value for the column that has a default value ("C3" in this case).

     

    0684Q00000ArKXQQA3.png

     

     

  2. Write features that are missing values using an SQLExecutor using an SQL INSERT statement

     

    0684Q00000ArKboQAF.png

     

    Notice: C3 (the column with a default value set) isn't included in the insert statement and the values are set by the attribute values of the current feature.

     

    By inserting values from an SQL statement, you are allowing the default value to be set by the database as this is the equivalent to executing the SQL statement directly in PGAdmin.

     

     

  3. Lastly, connect the Failed port of the Tester to a PostgreSQL writer. Since these features already have attribute values for the C3 attribute they can be sent to a writer.

     

    0684Q00000ArKaXQAV.png

This approach allows the database to set the default value based on the column properties in PGAdmin.

Method 2:

Use a transformer to set the default value and write features using a PostgreSQL writer. This could be done using a number of different transformers, but this example uses the NullAttributeMapper to set the default value before writing features with the PostgreSQL writer.

0684Q00000ArKZAQA3.png

 

You could also use conditional statements in a transformer like the AttributeManager.

I have attached a sample workspace that demonstrates how this works. If you want to try running the workspace as is, first create a new table in PGAdmin in the public schema called "TestCreateColumns" with 4 columns:

Column NameTypeC1TextC2CharC3Double - set the default value on this column - I used 100C4Double

Next, run the workspace (set your database connection when prompted) with one of the bookmarks enabled and view the output in PGAdmin.

Template: Insert_Default_Values.fmwt

Hi @chrisatsafe, 

 

I have the same problem as @savoierider, I don't want the PostgisWriter to specify columns I don't have in attribute definition. Some attributes have Default values in the destination table that I can't calculate in FME.

I tried the workaround with the SQLExecutor but I don't understand how to write the geometry with it.

With 

INSERT INTO "schema"."table"("ATTR1","ATTR2","ATTR3")VALUES ('@Value(attr_1)','@Value(attr_2)','@Value(attr_3)')

I only get the attributes values, not the geometry.

Any advice ?

Hi @chrisatsafe, 

 

I have the same problem as @savoierider, I don't want the PostgisWriter to specify columns I don't have in attribute definition. Some attributes have Default values in the destination table that I can't calculate in FME.

I tried the workaround with the SQLExecutor but I don't understand how to write the geometry with it.

With 

INSERT INTO "schema"."table"("ATTR1","ATTR2","ATTR3")VALUES ('@Value(attr_1)','@Value(attr_2)','@Value(attr_3)')

I only get the attributes values, not the geometry.

Any advice ?

Hi @p.jeremie,

I suspect the same workaround could work if you first extract the geometry with the GeometryExtractor using the OGC Well Known Text Geometry Encoding before inserting the values. http://postgis.net/workshops/postgis-intro/geometries.html 

If not, perhaps you could use the FeatureWriter to write the geometry features to PostGIS then connect an SQLExecutor to Update the ATTR1, ATTR2, ATTR3 values.

Hi @p.jeremie,

I suspect the same workaround could work if you first extract the geometry with the GeometryExtractor using the OGC Well Known Text Geometry Encoding before inserting the values. http://postgis.net/workshops/postgis-intro/geometries.html

If not, perhaps you could use the FeatureWriter to write the geometry features to PostGIS then connect an SQLExecutor to Update the ATTR1, ATTR2, ATTR3 values.

Thanks for the idea @chrisatsafe. I'll give it a try when I get back to work :)

I'm pleased to let you know that in FME 2022 betas build 22220 or newer, we have added support for allowing default values to be written to PostGreSQL/PostGIS.

There is a new writer parameter under Advanced > Missing Attribute Behaviour:

If set to Insert null, the writer inserts NULL values for column values if attributes are missing or NULL on incoming features. 

If set to Insert column default value, the writer inserts the default value for a column when the incoming feature has a missing or NULL attribute. 

Note: This parameter is only enabled when “Bulk Insert” is set to No.

PostGreSQL_Default_value

I'm pleased to let you know that in FME 2022 betas build 22220 or newer, we have added support for allowing default values to be written to PostGreSQL/PostGIS.

There is a new writer parameter under Advanced > Missing Attribute Behaviour:

If set to Insert null, the writer inserts NULL values for column values if attributes are missing or NULL on incoming features. 

If set to Insert column default value, the writer inserts the default value for a column when the incoming feature has a missing or NULL attribute. 

Note: This parameter is only enabled when “Bulk Insert” is set to No.

PostGreSQL_Default_value

excellent! 💪 👋

I'm pleased to let you know that in FME 2022 betas build 22220 or newer, we have added support for allowing default values to be written to PostGreSQL/PostGIS.

There is a new writer parameter under Advanced > Missing Attribute Behaviour:

If set to Insert null, the writer inserts NULL values for column values if attributes are missing or NULL on incoming features. 

If set to Insert column default value, the writer inserts the default value for a column when the incoming feature has a missing or NULL attribute. 

Note: This parameter is only enabled when “Bulk Insert” is set to No.

PostGreSQL_Default_value

@andreaatsafe​ A little bit early for christmas but what a wonderful news !!

Thanks a lot for this feature, I will give it a try as soon as fme 2022 will be released

I'm pleased to let you know that in FME 2022 betas build 22220 or newer, we have added support for allowing default values to be written to PostGreSQL/PostGIS.

There is a new writer parameter under Advanced > Missing Attribute Behaviour:

If set to Insert null, the writer inserts NULL values for column values if attributes are missing or NULL on incoming features. 

If set to Insert column default value, the writer inserts the default value for a column when the incoming feature has a missing or NULL attribute. 

Note: This parameter is only enabled when “Bulk Insert” is set to No.

PostGreSQL_Default_value

Amazing, thank you very much !

An other feature (for the future) could be "Do not use attributes not listed in the user parameter tab".

It would avoid using attribute that are into the table but not mentionned into the feature type.

I'm pleased to let you know that in FME 2022 betas build 22220 or newer, we have added support for allowing default values to be written to PostGreSQL/PostGIS.

There is a new writer parameter under Advanced > Missing Attribute Behaviour:

If set to Insert null, the writer inserts NULL values for column values if attributes are missing or NULL on incoming features. 

If set to Insert column default value, the writer inserts the default value for a column when the incoming feature has a missing or NULL attribute. 

Note: This parameter is only enabled when “Bulk Insert” is set to No.

PostGreSQL_Default_value

That's great, thanks a lot!

For the record, this new parameter is only available when adding a new writer (not when updating paramters on an existing one).

Reply


Community Stats

30,162
Posts
114,511
Replies
37,820
Members
Terms & ConditionsCookie settings
A product of
Safe Software respectfully acknowledges that we live, learn and work on the traditional and unceded territories of the Kwantlen, Katzie, and Semiahmoo First Nations.