Feature Operations

Many writers share a Feature Operation specification, which determines how features will be written into the destination table.

Note  Feature Operation supersedes Writer Mode.

Supported feature operations are described below. Note that the described behavior can be dependent on the selected options, as well as the underlying table properties.

Option

Description

If the Row Does Not Exist

If the Row Exists

Insert

The writer appends a new row to a table using input feature attributes.

The writer creates a new row using input feature attributes.

Not always applicable: if the table does not have a unique key or it has an automatically generated unique key, insertion is always possible.

The database cannot violate its key constraints; therefore, errors can occur on row insertion. For example, if there is a unique key and a user specifies the value with the feature, and the feature already exists, then FME Workbench logs an error. This error might be in the form of a rejected feature, or the database may stop processing altogether.

Update

The writer updates existing row(s) in a table using input feature attributes.

A selection method must be specified in the Row Selection group.

The writer rejects the input feature or logs an error if it is unable to continue.

The writer only changes values of the existing row(s) corresponding to the input feature that differ from the input feature.

Upsert

The writer appends a new row or updates an existing row(s) using input feature attributes and/or geometries.

A selection method must be specified in the Row Selection group.

The writer creates a new row using input feature attributes and/or geometries.

The writer changes values of the existing row(s) corresponding to the input feature that differ from the input feature.

It leaves other attributes untouched.

Upsert for:

  • PostGIS
  • PostGIS Raster
  • PostgreSQL
  • SpatiaLite

The writer updates the existing row(s) in a table using input feature attributes if the specified conflict arises. If no conflicts arise, the writer appends a new row to a table using input feature attributes.

The writer creates a new row using input feature attributes.

If the specified conflict arises, the writer will change only values of the existing row(s) corresponding to the input feature that differ from the input feature.

If other conflicts arise, the writer will reject the input feature or log an error if it is unable to continue.

Delete

The writer deletes an existing row(s) in a table.

A selection method must be specified in the Row Selection group.

The writer rejects the input feature or logs an error if it is unable to continue.

The writer deletes existing row(s) corresponding to the input feature.

Put

The writer appends a new row to a table using input feature attributes and/or geometries.

If a row with the same primary identifier already exists, the existing row is overwritten by the new row.

A selection method must be specified in the Row Selection group.

The writer creates a new row using input feature attributes and/or geometries.

The writer deletes the existing row corresponding to the input feature and appends a new row using input feature attributes and/or geometries.

fme_db_
operation

The feature operation will be determined by the attribute fme_db_operation on each input feature.

A selection method must be specified in the Row Selection group.

The value of fme_db_operation will be processed according to the steps below.

Note   The processing steps listed below depend on a format's available Feature Operation options.
  • If the value is null, empty, or missing, it will be treated as an Insert.
  • The value will next be matched to Insert, Update, Upsert, Put, and Delete, case-insensitively.
  • If there is no match, the feature will be rejected.
  • If there is a match, the matched feature operation will be performed on the feature.

Note about earlier FME versions: To use fme_db_operation, you must set Feature Operation to this option. In previous versions of FME, you could set fme_db_operation when the destination feature type was set to Insert, Update, Upsert, or Delete. Doing this now will cause feature rejection.

 The action depends on the operation; however, in general, if nothing is specified, the value is treated as an Insert. The value is treated as an Insert.

Update Spatial Column(s) is a writer format parameter that is in effect only when the feature operation for a feature is Update. Possible values for this parameter are:

  • Yes: The spatial column(s) specified by the user will be updated. IFMENulls will be written as null values and replace existing spatial values. Some exceptions exist for multiple geometry column handling.
  • No: The spatial column(s) will not be updated.

Row Selection is a writer format parameter group that is in effect only when the feature operation for a feature is Update, Upsert, or Delete. When modifying a table, a condition needs to be specified for selecting which rows to operate on. This parameter group offers two ways to construct the selection condition:

  • Match Columns: The column(s) chosen in the corresponding column picker dialog will be used for matching destination rows. All matching rows will be selected for update, upsert, or delete. If any feature attributes corresponding to the specified match columns contain null or missing values, the feature will be rejected.

  • WHERE Clause: The corresponding WHERE Clause Builder lets you construct a WHERE clause. You can also type in a WHERE clause inline, without launching the WHERE Clause Builder. (It is optional to start the clause with the keyword WHERE.)

  • The WHERE Clause Builder makes it easy for users to reference feature attribute values, destination table columns, and invoke FME functions. The WHERE clause is first evaluated as an FME expression, before being applied onto the destination table.
  • The user can set the WHERE clause to any attribute on the feature. For existing FME users, the familiar fme_where attribute now must be specified via a WHERE Clause for it to take effect (see example above).
  • If the WHERE clause is invalid, the feature will be rejected or the translation will fail, depending on the specific writer.
  • For advanced users, conditional WHERE clauses can be created through the Conditional Value editor, to accommodate different groups of features.

See also: Writer Mode