Bug: Issue with Composite Primary Keys in _upsertBatch for PostgreSQL – Only the First Index Field Is Considered

[campio97]

PHP Version

8.4

CodeIgniter4 Version

4.6.0

CodeIgniter4 Installation Method

Composer (using codeigniter4/appstarter)

Which operating systems have you tested for this bug?

macOS

Which server did you use?

apache

Database

PostgreSQL 17.2

What happened?

Hello,

I'm reporting a potential bug in CodeIgniter 4 related to the _upsertBatch function when used with PostgreSQL. It appears that the function only considers the first field of an index for conflict resolution, which causes issues when dealing with composite primary keys or tables with multiple unique indexes.

Details:

In the _upsertBatch function, the following code snippet suggests that only one index (and, in practice, only the first field of that index) is used:

foreach (array_map(static fn($index) => $index->fields, $allIndexes) as $index) {
    $constraints[] = current($index);
    // only one index can be used?
    break;
}

Because of this, if a table has a composite primary key (or multiple unique constraints), the generated SQL will include only the first field from the index in the ON CONFLICT clause. This behavior may lead to unexpected failures or incorrect updates during upsert operations.

Steps to Reproduce

Create a PostgreSQL table with a composite primary key or with multiple unique indexes.
Use the upsertBatch method to insert/update data in this table.
Observe that the conflict resolution logic only uses the first field of the composite key, which can lead to errors or improper upsert behavior.

Expected Output

All relevant fields of the composite key (or the chosen unique index) should be used in the ON CONFLICT clause to correctly identify conflicts and perform the update action.

Anything else?

This issue might be related to the comment // only one index can be used? present in the code. It suggests that the current implementation might be a temporary workaround or an oversight that did not fully account for composite keys.