Avoiding the use of generated always column in a merge statement

Overview

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

Assume, data should be copied with XDM and should be applied in the target using the fill mode Insert and on key collision: Update target row. In this case, a MERGE statement will be executed by XDM. On target table, the primary key column id is defined as GENERATED ALWAYS.

During execution, an 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”, as this is always generated by the database and cannot be edited manually. At the same time, however, the primary key, i.e. the column “id”, should be used in the condition. The result would therefore be an invalid merge statement.

Prerequisites

  • Target table with a generated always id as part of the primary key

Multiple Steps

To solve the problem and successful migrate the data, the id column should be excluded from the data transfer. Furthermore, it is necessary to specify an alternative key which should be used in the condition in the merge statement. These two steps are described below.

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 will not be selected and therefore not transferred.

Step 2: Define a User-Defined Primary Key

XDM needs an unique key to identify a single row in the target table uniquely. This is necessary to identify, if a row already exists in the target table and has to be updated, or if it not exists and needs to be inserted. Since id is excluded, alternative unique key called User-defined primary key must be defined must be defined in XDM for the target table.

Prerequisite: There should be an existing unique constraint or unique index on the columns which should be used as user-defined primary key in the target table. Otherwise performance may be very slow.

For this example, assume that a unique index containing 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 will use these two columns as condition in the merge statement to identify, if an existing row should be updated or a new row must be inserted.

Benefits

  • Prevents PSQLException caused by mismatched column counts.

  • Ensures compatibility with GENERATED ALWAYS columns.

  • Uses existing unique constraints to avoid duplicate rows.

  • Provides a reproducible migration setup for future transfers.

Conclusion

When copying data between tables, special attention is required for GENERATED ALWAYS primary keys. By excluding such columns and configuring a User-defined Primary Key, XDM can reliably perform 1:1 migrations without errors.

This solution ensures stable operation and preserves data integrity across source and target systems.