Zenn
Closed15

CodeIgniter4.4.5 Error when updateBatch() a single record containing TS TZ columns (PostgreSQL 9.2)

PHP
PostgreSQL
codeigniter
codeigniter4
naentenaente

複合主キーテーブル

CREATE TABLE test_table
(
    id1       integer not null,
    id2       integer not null,
    char      char(10),
    varchar   varchar(10),
    timestamp timestamp(0) with time zone,
    primary key (id1, id2)
);
naentenaente

複合主キーテーブルを扱う為、CodeIgniter Model は使用しない

app\Models\TestTableModel.php
<?php

namespace App\Models;

use CodeIgniter\Database\BaseBuilder;
use CodeIgniter\Database\BaseConnection;

class TestTableModel
{
    /**
     * @var BaseConnection
     */
    protected BaseConnection $db;

    /**
     * @var BaseBuilder
     */
    private BaseBuilder $builder;

    public function __construct()
    {
        $this->db = db_connect();
        $this->builder = $this->db->table('test_table');
    }

    /**
     * @param $set
     * @return void
     */
    public function insertBatch($set)
    {
        $this->builder->insertBatch($set);
    }

    /**
     * @param $set
     * @param $constraints
     * @return void
     */
    public function updateBatch($set, $constraints)
    {
        $this->builder->updateBatch($set, $constraints);
    }
}
naentenaente
app\Controllers\TestController.php
<?php

namespace App\Controllers;

use App\Controllers\BaseController;
use App\Models\TestTableModel;
use CodeIgniter\HTTP\ResponseInterface;

class TestController extends BaseController
{
    public function index()
    {
        //
    }

    public function testInsertBatch1()
    {
        $model = new TestTableModel();
        $set = [
            [
                'id1'       => 1,
                'id2'       => 1,
                'char'      => 'Foo',
                'varchar'   => 'Foo',
                'timestamp' => 'now()',
            ],
            [
                'id1'       => 1,
                'id2'       => 2,
                'char'      => 'Bar',
                'varchar'   => 'Bar',
                'timestamp' => 'now()',
            ],
            [
                'id1'       => 1,
                'id2'       => 3,
                'char'      => 'Baz',
                'varchar'   => 'Baz',
                'timestamp' => 'now()',
            ],
        ];
        $model->insertBatch($set);
    }

    public function testInsertBatch2()
    {
        $model = new TestTableModel();
        $set = [
            [
                'id1'       => 1,
                'id2'       => 4,
                'char'      => 'Qux',
                'varchar'   => 'Qux',
                'timestamp' => 'now()',
            ],
        ];
        $model->insertBatch($set);
    }

    public function testUpdateBatch1()
    {
        $model = new TestTableModel();
        $set = [
            [
                'id1'       => 1,
                'id2'       => 1,
                'char'      => 'Modify Foo',
                'varchar'   => 'Modify Foo',
                'timestamp' => 'now()',
            ],
            [
                'id1'       => 1,
                'id2'       => 2,
                'char'      => 'Modify Bar',
                'varchar'   => 'Modify Bar',
                'timestamp' => 'now()',
            ],
            [
                'id1'       => 1,
                'id2'       => 3,
                'char'      => 'Modify Baz',
                'varchar'   => 'Modify Baz',
                'timestamp' => 'now()',
            ],
        ];
        $constraints = [
            'id1',
            'id2',
        ];
        $model->updateBatch($set, $constraints);
    }

    public function testUpdateBatch2()
    {
        $model = new TestTableModel();
        $set = [
            [
                'id1'       => 1,
                'id2'       => 4,
                'char'      => 'Modify Qux',
                'varchar'   => 'Modify Qux',
                'timestamp' => 'now()',
            ],
        ];
        $constraints = [
            'id1',
            'id2',
        ];
        $model->updateBatch($set, $constraints);
    }
}
naentenaente
Method PostgreSQL 9.2.24 PostgreSQL 13.11
testInsertBatch1 success success
testUpdateBatch1 success success
testInsertBatch2 success success
testUpdateBatch2 fail success

CodeIgniter\Database\Exceptions\DatabaseException
pg_query(): Query failed: ERROR: failed to find conversion function from unknown to timestamp with time zone

SYSTEMPATH\Database\BaseConnection.php at line 647

naentenaente

以下のように timestamp(0) with time zone カラムへの更新を除外すると
PostgreSQL 9.2.24 でも updateBatch による単一レコードの更新が可能

app\Controllers\TestController.php
public function testUpdateBatch3()
{
    $model = new TestTableModel();
    $set = [
        [
            'id1'       => 1,
            'id2'       => 4,
            'char'      => 'Modify Qux',
            'varchar'   => 'Modify Qux',
        ],
    ];
    $constraints = [
        'id1',
        'id2',
    ];
    $model->updateBatch($set, $constraints);
}
naentenaente

アドバイスいただいた RawSql を使用することで解決した。

app\Controllers\TestController.php
use CodeIgniter\Database\RawSql;
app\Controllers\TestController.php
'timestamp' => new RawSql('now()'),
naentenaente
CREATE TABLE test
(
    id         integer                     not null,
    created_at timestamp(0) with time zone not null,
    primary key (id)
);
naentenaente
app\Models\TestModel.php
<?php

namespace App\Models;

use CodeIgniter\Database\BaseBuilder;
use CodeIgniter\Database\BaseConnection;

class TestModel
{
    /**
     * @var BaseConnection
     */
    protected BaseConnection $db;

    /**
     * @var BaseBuilder
     */
    private BaseBuilder $builder;

    public function __construct()
    {
        $this->db = db_connect();
        $this->builder = $this->db->table('test');
    }

    /**
     * @param $set
     * @return void
     */
    public function insertBatch($set)
    {
        $this->builder->insertBatch($set);
    }

    /**
     * @param $set
     * @param $constraints
     * @return void
     */
    public function updateBatch($set, $constraints)
    {
        $this->builder->updateBatch($set, $constraints);
    }
}
naentenaente
app\Controllers\TestController.php
<?php

namespace App\Controllers;

use App\Controllers\BaseController;
use App\Models\TestModel;
use CodeIgniter\Database\RawSql;
use CodeIgniter\HTTP\ResponseInterface;

class TestController extends BaseController
{
    public function testInsertBatch1()
    {
        $model = new TestModel();
        $set = [
            [
                'id'         => 1,
                'created_at' => 'now',
            ],
        ];
        $model->insertBatch($set);
    }

    public function testInsertBatch2()
    {
        $model = new TestModel();
        $set = [
            [
                'id'         => 2,
                'created_at' => 'now()',
            ],
        ];
        $model->insertBatch($set);
    }

    public function testInsertBatch3()
    {
        $model = new TestModel();
        $set = [
            [
                'id'         => 3,
                'created_at' => new RawSql('now'),
            ],
        ];
        $model->insertBatch($set);
    }

    public function testInsertBatch4()
    {
        $model = new TestModel();
        $set = [
            [
                'id'         => 4,
                'created_at' => new RawSql('now()'),
            ],
        ];
        $model->insertBatch($set);
    }

    public function testUpdateBatch1()
    {
        $model = new TestModel();
        $set = [
            [
                'id'         => 1,
                'created_at' => 'now',
            ],
        ];
        $constraints = [
            'id',
        ];
        $model->updateBatch($set, $constraints);
    }

    public function testUpdateBatch2()
    {
        $model = new TestModel();
        $set = [
            [
                'id'         => 2,
                'created_at' => 'now()',
            ],
        ];
        $constraints = [
            'id',
        ];
        $model->updateBatch($set, $constraints);
    }

    public function testUpdateBatch3()
    {
        $model = new TestModel();
        $set = [
            [
                'id'         => 3,
                'created_at' => new RawSql('now'),
            ],
        ];
        $constraints = [
            'id',
        ];
        $model->updateBatch($set, $constraints);
    }

    public function testUpdateBatch4()
    {
        $model = new TestModel();
        $set = [
            [
                'id'         => 4,
                'created_at' => new RawSql('now()'),
            ],
        ];
        $constraints = [
            'id',
        ];
        $model->updateBatch($set, $constraints);
    }
}
naentenaente

PostgreSQL 13.11

insertBatch

1
INSERT INTO "test"("created_at", "id") VALUES ('now',1)
2
INSERT INTO "test"("created_at", "id") VALUES ('now()',2)
3
INSERT INTO "test"("created_at", "id") VALUES (now,3)
4
INSERT INTO "test"("created_at", "id") VALUES (now(),4)

3 は 42703 未定義列エラー
[42703] ERROR: 列"now"は存在しません

naentenaente

PostgreSQL 13.11

updateBatch

1
UPDATE "test"
SET "created_at" = CAST(_u."created_at" AS TIMESTAMP WITH TIME ZONE)
FROM (SELECT 'now' "created_at", 1 "id") _u
WHERE "test"."id" = CAST(_u."id" AS INTEGER)
2
UPDATE "test"
SET "created_at" = CAST(_u."created_at" AS TIMESTAMP WITH TIME ZONE)
FROM (SELECT 'now()' "created_at", 2 "id") _u
WHERE "test"."id" = CAST(_u."id" AS INTEGER)
3
UPDATE "test"
SET "created_at" = CAST(_u."created_at" AS TIMESTAMP WITH TIME ZONE)
FROM (SELECT now "created_at", 3 "id") _u
WHERE "test"."id" = CAST(_u."id" AS INTEGER)
4
UPDATE "test"
SET "created_at" = CAST(_u."created_at" AS TIMESTAMP WITH TIME ZONE)
FROM (SELECT now() "created_at", 4 "id") _u
WHERE "test"."id" = CAST(_u."id" AS INTEGER)

3 は 42703 未定義列エラー
[42703] ERROR: 列"now"は存在しません

naentenaente

PostgreSQL 9.2.24

insertBatch

3 は 42703 未定義列エラー
[42703] ERROR: 列"now"は存在しません

naentenaente

PostgreSQL 9.2.24

updateBatch

1 と 2 は XX000 内部エラー
[XX000] ERROR: failed to find conversion function from unknown to timestamp with time zone

3 は 42703 未定義列エラー
[42703] ERROR: 列"now"は存在しません

このスクラップは2024/02/26にクローズされました