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
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
