Handling GENERATED ALWAYS Columns

Overview

This guide describes how to merge data when a column defined as GENERATED ALWAYS is the primary key or part of it.

Assume that data should be copied with XDM and applied in the target using the fill mode Insert and on key collision: Update target row. In this case, XDM executes a MERGE statement.

In the target table, the primary key column id is defined as GENERATED ALWAYS.

During execution, the following error may occur:

PSQLException: The column index is out of range: 67, number of columns: 66.

Root cause:

A MERGE statement consists of an INSERT statement and an UPDATE statement, as well as a condition that determines which of the two statements should be executed. In this case, the problem is that neither the INSERT nor the UPDATE statement can contain the column id, because this column is always generated by the database and cannot be edited manually. At the same time, the primary key column id would normally be used in the condition of the MERGE statement. The result would therefore be an invalid MERGE statement.

Prerequisites

  • A target table with a GENERATED ALWAYS column as primary key or as part of the primary key.

  • Alternative columns that uniquely identify a row in the target table.

  • A defined CTC Task where the problem has to be resolved.

Steps

To solve the problem and successfully migrate the data:

  1. Exclude the generated id column from the data transfer.

  2. Define an alternative key that XDM can use in the MERGE condition.

Step 1: Exclude the id Column

The id column in the target table is defined as GENERATED ALWAYS.

It must be excluded from the data transfer.

Action:

Add an Exclude Rule in the CTC Task.

  • Type: Column

  • Scope: Source

  • Table: "<SourceSchema>"."<SourceName>"

  • Column: id

Result:

The column id is not selected and therefore not transferred.

Step 2: Define a User-Defined Primary Key

XDM needs a unique key to identify a single row in the target table.

This is necessary to decide whether:

  • a row already exists and should be updated, or

  • a row does not exist and should be inserted.

Since id is excluded, an alternative unique key must be defined in XDM.

This key is called a User-defined Primary Key.

The target table should have an existing unique constraint or unique index on the columns used as the User-defined Primary Key. Otherwise, performance may be very slow.

For this example, assume that a unique index exists on the columns DATA1 and DATA2.

Action:

Configure a User-defined Primary Key in the Task Template.

  • Scope: Target

  • Table: "<TargetSchema>"."<TargetName>"

  • Column: DATA1,DATA2

Result:

XDM uses these columns in the MERGE condition to decide whether an existing row should be updated or a new row should be inserted.

Benefits

This approach provides the following benefits:

  • Prevents errors caused by invalid MERGE statements with GENERATED ALWAYS columns.

  • Ensures compatibility with generated primary key columns.

  • Allows XDM to update or insert rows using an alternative unique key.

  • Uses existing unique constraints or indexes to avoid duplicate rows.

Conclusion

When copying data into tables with GENERATED ALWAYS primary keys, the generated columns must not be inserted or updated manually.

By excluding these columns and configuring a User-defined Primary Key, XDM can generate a valid MERGE statement and reliably migrate the data.

This ensures stable execution and preserves data integrity in the target system.