Type casting in PostgreSQL with updateBatch()

Hello, I have a weird issue and couldn't even debug it. I have a update batch command

Code:

$builder = $this->db->table( 'hosts');
$builder->onConstraint($whereKey);
 $builder->setData($dbData);
 $update = $builder->updateBatch();

This is dbdata:

Code:

array(("host_id"=1231234,"update_date"='1111-11-11 11:11'),...)

this is $wherekey

Code:

"host_id"

this is the issue I got:

Code:

ERROR - 2023-11-24 15:16:06 --> ErrorException: pg_query(): Query failed: ERROR:  operator does not exist: bigint = text
LINE 8: WHERE "hosts"."host_id" = _u."host_id"
                                ^
HINT:  No operator matches the given name and argument types. You might need to add explicit type casts. in /.../web/system/Database/Postgre/Connection.php:187
Stack trace:
#0 [internal function]: CodeIgniter\Debug\Exceptions->errorHandler()
#1 /../web/system/Database/Postgre/Connection.php(187): pg_query()
#2 /../web/system/Database/BaseConnection.php(693): CodeIgniter\Database\Postgre\Connection->execute()

host_id is a bigint element on postgres. It was working well with CI 3 and I am working on migrating it to CI4 for days and got stuck on this one.

---

result of this :

Code:

$builder->getCompiledUpdate();

is

Code:

Array to string conversion

---

I finaly managed to identify the issue. CI 4 is not able to produce proper updateBatch query on following scenario:

Code:

UPDATE "hosts"
SET
"update_date" = _u."update_date"
FROM (
SELECT '2448114396435166946' "host_id", '2023-11-24 16:50:40' "update_date" UNION ALL
SELECT '2448114396437626572' "host_id", '2023-11-24 16:50:40' "update_date"
) _u
WHERE "hosts"."host_id" = _u."host_id"

Postgresql got error like this,
ERROR:  operator does not exist: bigint = text
LINE 8: WHERE "hosts"."host_id" = _u."host_id"
                                ^
HINT:  No operator matches the given name and argument types. You might need to add explicit type casts.

SQL state: 42883
Character: 251
Total rows: 0 of 0
Query complete 00:00:00.049
Ln 8, Col 39

then I remove Single quotes manually from IDs like

Code:

UPDATE "hosts"
SET
"update_date" = _u."update_date"
FROM (
SELECT 2448114396435166946 "host_id", '2023-11-24 16:50:40' "update_date" UNION ALL
SELECT 2448114396437626572 "host_id", '2023-11-24 16:50:40' "update_date"
) _u
WHERE "hosts"."host_id" = _u."host_id"

now it got errors for timestamp

Code:

ERROR:  operator does not exist: bigint = text
LINE 8: WHERE "hosts"."host_id" = _u."host_id"
                                ^
HINT:  No operator matches the given name and argument types. You might need to add explicit type casts.

SQL state: 42883
Character: 251
Total rows: 0 of 0
Query complete 00:00:00.049
Ln 8, Col 39

---

When I changed my query manually to this one, it works:

Code:

UPDATE "hosts"
SET
"update_date" = _u."update_date"
FROM (
SELECT '2448114396435166946'::BIGINT "host_id", '2023-11-24 16:50:40'::DATE "update_date" UNION ALL
SELECT '2448114396437626572'::BIGINT "host_id", '2023-11-24 16:50:40'::DATE "update_date"
) _u
WHERE "hosts"."host_id" = _u."host_id"

---

On CodeIgniter 3 , it was working well and it was using "case When" expressions on creating batch queries

---

TLDR: I use PostgreSQL 16

---

Host_id part is fixed when I added (int) casting to my values on array. But I am still struggling with datetime part. Tested lots of stuff like strtotime, now(), int, date(format,mydate) etc. But couldn't figure out it

---

I create time from date('Y-m-d H:i') and submit but its considered as string

column "update_date" is of type timestamp without time zone but expression is of type string

my solution is here;
https://files.slack.com/files-tmb/T46QYU...ge_720.png
if anyone has experience same

my solution is here;
https://files.slack.com/files-tmb/T46QYU...ge_720.png
if anyone has experience same

I have the same issue when calling batchUpdate() on a postgresql database. In my case the query fails because of the updated_at field, which is passed in the query as a string. Also when trying to batch update a column of type json generates an invalid query.

I hope my solution will work for you too. As I patched it, now I am passing updated_at (date fiels) like $db['updated_at'] = array($date,'date')
on my patch if its array, its parses and applied second index to query itself to cast its type like '1111-11-11 11 11:1'::date

@byrallier Many users cannot access the link, and chat in Slack will be unaccessible soon.
It would be nice to paste the diff as text in here.

ci-phpunit-test | CodeIgniter Testing Guide | CodeIgniter 3 to 4 Upgrade Helper

I have this issue. i dunno what to do.

my whole project is now stuck at this point. cant go forward without having a solution to this. i nerver had this type of issue in ci3.

i suggest this kind of way casting types, $builder->updateBatch($data, ['title', 'author', 'data:jsonb']);

on CI4, There are new form of creating SQL queries and this is happen when you dont use type definitions on models. (i think) I dont know why anyone else is not experincing this, its critical I believe.

method: _updateBatch
change this line:
static fn ($key, $index) => $index . ' ' . $key,
to:
static fn ($key, $index) => (is_array($index)?$index[0].'::'.str_replace("'","",$index[1]):$index) . ' ' . $key,
** simple explanation is, If a value of a row is array then apply its type from getting second index by interpolating sting like '2024-01-01'::date

Also I applied this one to _insertBatch. Now I send type information on an array for batch queries like;

array( [0]=>(123,'simon',array('2024-01-01',''date')), [1]=>(124,'byrallier',array('2024-01-02',''date'))...

free look to diff: https://files.slack.com/files-pri/T46QYU.../image.png

Hi @kenjis
Can you guys do something about this issue sooner? I know it's not appropriate to ask something that from you guys. I wish I had broad knowledge about the framework, so i can fix this. Man, im just stuck now. i have many crud modules that needs batch type updatings. it's a great pleasure if you guys can fix this issue and release a minor version fast.
Take a look at the solution im suggesting. 

PHP Code:

<?php

$data = [
    [
        'id:BIGINT'  => 1,
        'data:JSONB' => '{"books":{}}'
    ],
    [
        'id:BIGINT'  => 2,
        'data:JSONB' => '{"books":{}}'
    ],
];

$builder->updateBatch($data, ['id']); 


---

on CI4, There are new form of creating SQL queries and this is happen when you dont use type definitions on models. (i think) I dont know why anyone else is not experincing this, its critical I believe.

method: _updateBatch
change this line:
static fn ($key, $index) => $index . ' ' . $key,
to:
static fn ($key, $index) => (is_array($index)?$index[0].'::'.str_replace("'","",$index[1]):$index) . ' ' . $key,
** simple explanation is, If a value of a row is array then apply its type from getting second index by interpolating sting like '2024-01-01'::date

Also I applied this one to _insertBatch. Now I send type information on an array for  batch queries like;

array( [0]=>(123,'simon',array('2024-01-01',''date')), [1]=>(124,'byrallier',array('2024-01-02',''date'))...

free look to diff: https://files.slack.com/files-pri/T46QYU.../image.png

Thanks for the insight, i may look into this.

dear @kenjis as mrWhite says it is ok I had such a batch demand one time ago that I postponed and later used my way but right now I need it

PHP Code:

<?php

$data = [
    [
        'id:BIGINT'  => 1,
        'data:JSONB' => '{"books":{}}''//json fr array 
    ],
    [
        'id: BIGINT'  => 2,
        'data:JSONB' => '{"books":{}}'//json  fr array 
    ],
];

$builder->updateBatch($data, ['id']); 


This is needed

---

@MrWhite can you PR this anyway

Codeigniter First, Codeigniter Then You!!

yekrinaDigitals

@MGatner Is there any possibility this bug gets fixed soon?

Thanks.