PostgreSQL Model Insert Function Issue

Dear fellow CodeIgniter Developer,
I found an issue with Codeigniter 4 (Version: 4.1.8) Model Insert Function with PostgreSQL.
I tried to search in google and did not find someone who has the same issue, so I post this new topic here.
I usually use MySQL with CodeIgniter 4 and the Model feature is work perfectly.
I must build an app using PostgreSQL and I tried to use the Model feature too with this database. Because the Model feature is perfect to manage the data.

I am using:
PHP 7.4.27
Apache 2.4.48
PostgreSQL 14.1
CodeIgniter 4.1.8

When I tried to insert data using Model, this error always appears:

Code:

<br />
<b>Fatal error</b>:  Uncaught CodeIgniter\Format\Exceptions\FormatException: Failed to parse json string, error: &quot;Type is not supported&quot;. in /Users/macuser/Sites/postgre-api/system/Format/JSONFormatter.php: 41
Stack trace:
#0 /Users/macuser/Sites/postgre-api/system/Format/JSONFormatter.php(41): CodeIgniter\Format\Exceptions\FormatException: :forInvalidJSON('Type is not sup...')
#1 /Users/macuser/Sites/postgre-api/system/API/ResponseTrait.php(341): CodeIgniter\Format\JSONFormatter-&gt;format(Array)
#2 /Users/macuser/Sites/postgre-api/system/API/ResponseTrait.php(99): CodeIgniter\Debug\Exceptions-&gt;format(Array)
#3 /Users/macuser/Sites/postgre-api/system/Debug/Exceptions.php(115): CodeIgniter\Debug\Exceptions-&gt;respond(Array,
500)
#4 [internal function
]: CodeIgniter\Debug\Exceptions-&gt;exceptionHandler(Object(ErrorException))
#5 {main
}
  thrown in <b>/Users/macuser/Sites/postgre-api/system/Format/JSONFormatter.php</b> on line <b>41</b><br />
{
    "title": "ErrorException",
    "type": "ErrorException",
    "code": 500,
    "message": "Uncaught CodeIgniter\\Format\\Exceptions\\FormatException: Failed to parse json string, error: \"Type is not supported\". in /Users/macuser/Sites/postgre-api/system/Format/JSONFormatter.php:41\nStack trace:\n#0 /Users/macuser/Sites/postgre-api/system/Format/JSONFormatter.php(41): CodeIgniter\\Format\\Exceptions\\FormatException::forInvalidJSON('Type is not sup...')\n#1 /Users/macuser/Sites/postgre-api/system/API/ResponseTrait.php(341): CodeIgniter\\Format\\JSONFormatter->format(Array)\n#2 /Users/macuser/Sites/postgre-api/system/API/ResponseTrait.php(99): CodeIgniter\\Debug\\Exceptions->format(Array)\n#3 /Users/macuser/Sites/postgre-api/system/Debug/Exceptions.php(115): CodeIgniter\\Debug\\Exceptions->respond(Array, 500)\n#4 [internal function]: CodeIgniter\\Debug\\Exceptions->exceptionHandler(Object(ErrorException))\n#5 {main}\n  thrown",
    "file": "/Users/macuser/Sites/postgre-api/system/Format/JSONFormatter.php",
    "line": 41,
    "trace": [
        {
            "function": "shutdownHandler",
            "class": "CodeIgniter\\Debug\\Exceptions",
            "type": "->",
            "args": []
        }
    ]
}

The data was inserted, but the application always return this error. So I think this is not good at all.
When I tried to use query builder without a model in Controller, the insert function works well. The data was inserted and the application did not return this error. It always returns an error if using the Model insert function.
I tried to use the Model update and Model delete function and it works well. No error was returned. So I got confused why only the Model insert function returns the error.

After doing some research about the error, I found that an error in 

Code:

#1 /Users/macuser/Sites/postgre-api/system/API/ResponseTrait.php(341): CodeIgniter\Format\JSONFormatter->format(Array)

I tried to put a var_dump in here:

PHP Code:

if ($mime !== 'application/json') {
            // Recursively convert objects into associative arrays
            // Conversion not required for JSONFormatter
            $data = json_decode(json_encode($data), true);
        }
        var_dump($data);
        return $this->formatter->format($data); 


Then I found this error:

Code:

[
  "title"
]=>
string(14) "ErrorException"
[
  "type"
]=>
string(14) "ErrorException"
[
  "code"
]=>
int(500)
[
  "message"
]=>
string(76) "pg_query(): Query failed: ERROR:  lastval is not yet defined in this session"
[
  "file"
]=>
string(77) "/Users/macuser/Sites/postgre-api/system/Database/postgre/Connection.php"
[
  "line"
]=>
int(135)

I tried to search in google and I found that the Primary Key must be SERIAL type.
I tried to rebuild the table with serial and enable the auto-increment, but it generates an error because I must put an integer into the insert function for the Primary Key column.
Because of that, I tried to insert with a manual increment for the Primary Key column and it still generates the lastval error. The data was inserted into the database, but it generates lastval error.
So I conclude that this error is not about the SERIAL type in PostgreSQL. Because either SERIAL or VARCHAR type for Primary Key still generate the same error.

Am I doing something wrong here?

Here are my codes:
BaseController:

PHP Code:

<?php

namespace App\Controllers;

use CodeIgniter\Controller;
use CodeIgniter\HTTP\CLIRequest;
use CodeIgniter\HTTP\IncomingRequest;
use CodeIgniter\HTTP\RequestInterface;
use CodeIgniter\HTTP\ResponseInterface;
use Psr\Log\LoggerInterface;
use CodeIgniter\API\ResponseTrait;

/**
 * Class BaseController
 *
 * BaseController provides a convenient place for loading components
 * and performing functions that are needed by all your controllers.
 * Extend this class in any new controllers:
 *    class Home extends BaseController
 *
 * For security be sure to declare any new methods as protected or private.
 */
class BaseController extends Controller
{
  use ResponseTrait;
    /**
    * Instance of the main Request object.
    *
    * @var CLIRequest|IncomingRequest
    */
    protected $request;

    /**
    * An array of helpers to be loaded automatically upon
    * class instantiation. These helpers will be available
    * to all other controllers that extend BaseController.
    *
    * @var array
    */
    protected $helpers = [];

  protected $validation = '';

  function __construct () {
    $this->request = service('request');
    $this->validation = \Config\Services::validation();
  }

    /**
    * Constructor.
    */
    public function initController(RequestInterface $request, ResponseInterface $response, LoggerInterface $logger)
    {
        // Do Not Edit This Line
        parent::initController($request, $response, $logger);

        // Preload any models, libraries, etc, here.

        // E.g.: $this->session = \Config\Services::session();
    }
} 


Home.php Controller:

PHP Code:

<?php
namespace App\Controllers\Article;

use App\Controllers\BaseController;
use App\Models\Article\ArticleModel;
use DateTime;

class Home extends BaseController {
  private $articleModel;
  function __construct () {
    parent::__construct();
    $this->articleModel = new ArticleModel();
  }

  function index () {
    var_dump($this->articleModel->getData('data'));
  }

  function detail () {
    var_dump($this->articleModel->getData('detailData', ['id' => 'A61f3cb258b852']));
  }

  function insert () {
    $objDateTime = new DateTime();
    if ($this->articleModel->insertData('insertData', [
      'title' => 'Article',
      'content' => '<h1>Hello Article</h1>',
      'publishDate' => $objDateTime->format('Y-m-d'),
      'order' => 0,
      'show' => true,
      'archive' => false
    ]) === false) {
      return $this->fail($this->articleModel->generateErrorData());
    } else {
      return $this->respondCreated([], 'Article Insert Success');
    }
  }

  function update () {
    $objDateTime = new DateTime();
    if ($this->articleModel->updateData('updateData', [
      'id' => 'A61f3cb258b852',
      'title' => 'Article Updated',
      'content' => '<h1>Hello Article Updated</h1>',
      'publishDate' => $objDateTime->format('Y-m-d'),
      'show' => false
    ]) === false) {
      return $this->fail($this->articleModel->generateErrorData());
    } else {
      return $this->respondUpdated([], 'Article Update Success');
    }
  }

  function delete () {
    $this->articleModel->deleteData('deleteData', [
      'id' => 'A61f3cb258b852'
    ]);
  }
}
?>

BaseModel.php Model:

PHP Code:

<?php
namespace App\Models;

use CodeIgniter\Model;

class BaseModel extends Model {
  protected $useTimestamps = true;
  protected $createdField  = 'created_at';
  protected $updatedField  = 'updated_at';
  protected $deletedField  = 'deleted_at';

  protected $skipValidation = false;

  protected $tableAlias = [];

  protected $imagePath = '';
  protected $uploadImageConfig = [
    'imageQuality' => 90,
    'imageWidth' => 1024,
    'imageHeight' => 0
  ];

  function generateErrorData () {
    $errorsData = $this->errors();
    $result = [];

    if (isset($errorsData) && $errorsData !== '' && count($errorsData) > 0) {
      foreach ($errorsData as $key => $errorsDataRow):
        $keyResult = array_search($key, $this->tableAlias);
        if ($keyResult !== false) {
          $result[$keyResult] = $errorsDataRow;
        }
      endforeach;
    }

    return $result;
  }

  function getImagePath () {
    return $this->imagePath;
  }

  function getUploadImageConfig () {
    return $this->uploadImageConfig;
  }

  function _convertNullToEmptyString ($data) {
    if (is_array($data)) {
      foreach ($data as $key => $dataRow):
        if ($dataRow === null) {
          $data[$key] = '';
        }
      endforeach;
    }
    return $data;
  }
} 


ArticleModel.php Model:

PHP Code:

<?php
namespace App\Models\Article;

use App\Models\BaseModel;
use DateTime;

class ArticleModel extends BaseModel {
  protected $table = 'article_ms';
  protected $primaryKey = 'article_id';
  protected $returnType = 'App\Entities\Article\Article';
  protected $useSoftDeletes = false;

  protected $allowedFields = [
    'article_id',
    'article_title',
    'article_content',
    'article_publish_date',
    'article_order',
    'article_show',
    'article_archive'
  ];

  protected $validationRules = [
    'article_id' => [
      'label' => 'ID',
      'rules' => 'required'
    ],
    'article_title' => [
      'label' => 'title',
      'rules' => 'required'
    ],
    'article_content' => [
      'label' => 'Content',
      'rules' => 'required'
    ],
    'article_publish_date' => [
      'label' => 'Publish Date',
      'rules' => 'required'
    ]
  ];

  protected $tableAlias = [
    'title' => 'article_title',
    'content' => 'article_content',
    'publishDate' => 'article_publish_date',
    'order' => 'article_order',
    'show' => 'article_show',
    'archive' => 'article_archive'
  ];

  protected $imagePath = './img/article/';

  function __construct () {
    parent::__construct();
  }

  function getData ($flag = '', $data = [], $convertLabel = '') {
    $result = [];
    switch ($flag) {
      case 'data':
        $result = $this->findAll();
        break;
      case 'detailData':
        $result = $this->where('article_id', $data['id'])->first();
        break;
    }
    return $this->convertData($convertLabel, $result);
  }

  function convertData ($flag = '', $data) {
    $result = [];
    switch ($flag) {
      default:
        $result = $data;
        break;
    }

    return $result;
  }

  function insertData ($flag = '', $data) {
    switch ($flag) {
      case 'insertData':
        return $this->insert(new \App\Entities\Article\Article([
          'article_id' => uniqid('A'),
          'article_title' => $data['title'],
          'article_content' => $data['content'],
          'article_publish_date' => $data['publishDate'],
          'article_order' => $data['order'],
          'article_show' => $data['show'],
          'article_archive' => $data['archive']
        ]));
        break;
    }
  }

  function updateData ($flag = '', $data) {
    switch ($flag) {
      case 'updateData':
        return $this->update($data['id'], new \App\Entities\Article\Article([
          'article_title' => $data['title'],
          'article_content' => $data['content'],
          'article_publish_date' => $data['publishDate'],
          'article_show' => $data['show']
        ]));
        break;
    }
  }

  function deleteData ($flag = '', $data) {
    switch ($flag) {
      case 'deleteData':
        $this->delete($data['id']);
        break;
    }
  }
}
?>

Article Migration File:

PHP Code:

<?php

namespace App\Database\Migrations;

use CodeIgniter\Database\Migration;

class Article extends Migration
{
  public function up()
  {
    // article_ms
    $this->forge->addField([
      'article_id' => [
        'type' => 'VARCHAR',
        'constraint' => 255
      ],
      'article_title' => [
        'type' => 'VARCHAR',
        'constraint' => 255
      ],
      'article_content' => [
        'type' => 'TEXT'
      ],
      'article_publish_date' => [
        'type' => 'DATE'
      ],
      'article_order' => [
        'type' => 'BIGINT'
      ],
      'article_show' => [
        'type' => 'BOOLEAN'
      ],
      'article_archive' => [
        'type' => 'BOOLEAN'
      ],
      'created_at' => [
        'type' => 'DATETIME',
        'null' => true
      ],
      'updated_at' => [
        'type' => 'DATETIME',
        'null' => true
      ],
      'deleted_at' => [
        'type' => 'DATETIME',
        'null' => true
      ]
    ]);
    $this->forge->addKey('article_id', true);
    $this->forge->createTable('article_ms');
  }

  public function down()
  {
    // article_ms
    $this->forge->dropTable('article_ms');
  }
} 


I need help with this issue. Hope this topic will become a good discussion and can solve the issue.

CI 4.1.8
PgSQL 12.2
SERIAL works.
If your table does not use autoincrement then set the protected property useAutoIncrement to false.

CI 4.1.8
PgSQL 12.2
SERIAL works.
If your table does not use autoincrement then set the protected property useAutoIncrement to false.

WOW!!!!

You are the BEST!

Thank you so much for useAutoIncrement solutions.
I got it now.
Because I use VARCHAR for the Primary Key, the CodeIgniter automatically runs the useAutoIncrement if I don't set it as false.
If I use BIGINT or BIGSERIAL for the Primary Key, the useAutoIncrement must be true.
If I use VARCHAR for the Primary Key, the useAutoIncrement must be false.
This works well if I set the useAutoIncrement correctly.

Thank you so much for the solutions. You are the BEST!!!

Hello

Thank you for sharing this. I got it now.
Because I use VARCHAR for the Primary Key, the CodeIgniter automatically runs the useAutoIncrement if I don't set it as false.
If I use BIGINT or BIGSERIAL for the Primary Key, the useAutoIncrement must be true.
If I use VARCHAR for the Primary Key, the useAutoIncrement must be false.
This works well if I set the useAutoIncrement correctly.

Thank you.
(Golang Training)

I am also faced the same. Thanks for sharing this question . these solutions are really helpful.

Regards
Eden wheeler
Devops Certification