Bug: [Postgre] updateBatch() fails for mixed PHP value types in a TEXT column #10599

PHP Version

8.4

CodeIgniter4 Version

4.7.4

CodeIgniter4 Installation Method

Composer (using codeigniter4/appstarter)

Which operating systems have you tested for this bug?

Linux

Which server did you use?

cli

Environment

development

Database

PostgreSQL 16

What happened?

Query Builder's updateBatch() returns 0 and marks the transaction as failed
when rows supply a string and an integer for the same TEXT column.

PostgreSQL reports:
ERROR: invalid input syntax for type integer: "Example"

The generated UPDATE uses a UNION ALL subquery containing both
'Example' and 587 in its value column. PostgreSQL attempts to infer
that subquery column's type before the outer CAST(... AS TEXT).

Steps to Reproduce

Run with the PostgreSQL database connection:

$db = db_connect();
$db->transBegin();

try {
    $db->query('CREATE TEMP TABLE ci_batch_repro (id INTEGER PRIMARY KEY, value TEXT)');
    $db->query("INSERT INTO ci_batch_repro (id, value) VALUES (1, 'Before'), (2, '25')");

    $result = $db->table('ci_batch_repro')->updateBatch([
        ['id' => 1, 'value' => 'Example'],
        ['id' => 2, 'value' => 587],
    ], 'id');

    var_dump($result);            // 0
    var_dump($db->transStatus()); // false
    var_dump($db->error());
} finally {
    $db->transRollback();
}

This uses only CodeIgniter Query Builder, not the Settings package.

Expected Output

Both rows are updated successfully. The integer 587 can be stored in
the TEXT column as "587", and the transaction remains successful.

Anything else?

Generated SQL includes:

SELECT 1 "id", 'Example' "value" UNION ALL
SELECT 2 "id", 587 "value"

This differs from the PostgreSQL updateBatch() issue #7387, which was
closed by #8439; the behavior above still reproduces on v4.7.4.

I first encountered this through CodeIgniter Settings v2.4.0:
setMany() fails when updating existing settings containing both string
and integer values. The standalone Query Builder reproduction above
shows that the failure also occurs without the Settings package.