Seeding NULL values into NOT NULL columns with CI4-CodePudding

I'm having a problem with the CodeIgniter 4 seeder that I can't find any solution to so far. I used CodeIgniter 4 migrations to generate my tables and by default the columns are NOT NULL. There were no issues seeding the db in MySQL, even though I only inserted in some columns, which would have made anything else NULL.

The problem is that if I change to PostgreSQL, the migrations run fine. I just needed to change DOUBLE to NUMERIC. The seeder was not so easy. The first error I get is Message: pg_query(): Query failed: ERROR: null value in column "created_at" violates not-null constraint which is weird, since when using MySQL those columns are filled normally. But even if I manually tell my migrations to fill create_at and updated_at, I keep getting this error in any other columns that are NOT NULL, while in MySQL they were simply left empty.

I think I'm misunderstanding something with CI4, MySQL or PostgreSQL in this situation, and would be glad if someone could shed some light.

So to sum this up, I tried seeding a DB using PostgreSQL, which worked fine in MySQL, and it wouldn't let me not insert any value in the dozens of other fields not specified in the seeder. And I'm worried this will be a problem in forms and this sort of thing, since some fields not being filled will result in the same error.

For some clarification, here is the migration for a table in my DB:

class Configuracoes extends Migration
{
    public function up()
    {
        $this->forge->addField([
            'id_config'       => [
                'type'           => 'INT',
                'constraint'     => 9,
                'usigned'        => true,
                'auto_increment' => true,
            ],

            'nome_do_app' => [
                'type'       => 'VARCHAR',
                'constraint' => 128
            ],

            'tema' => [
                'type' => 'INT'
            ],

            'xNome' => [
                'type'       => 'VARCHAR',
                'constraint' => 128
            ],

            'xFant' => [
                'type'       => 'VARCHAR',
                'constraint' => 128
            ],

            'CNPJ' => [
                'type'       => 'VARCHAR',
                'constraint' => 14
            ],

            'telefone' => [
                'type'       => 'VARCHAR',
                'constraint' => 11
            ],

            'endereco' => [
                'type'       => 'VARCHAR',
                'constraint' => 256
            ],

            'arquivo-imagem-de-fundo-login' => [
                'type'       => 'VARCHAR',
                'constraint' => 128
            ],

            'logomarca' => [
                'type'       => 'VARCHAR',
                'constraint' => 128
            ],

            'created_at' => [
                'type'   => 'DATETIME'
            ],

            'updated_at' => [
                'type'   => 'DATETIME'
            ],

            'deleted_at' => [
                'type'   => 'DATETIME'
            ]
        ]);

        $this->forge->addKey('id_config', true);
        $this->forge->createTable('configuracoes');
    }

And the Seeder for this table:

        $this->db->table('configuracoes')->insert([
            'nome_do_app' => 'App',
            'tema'        => '4',
            'xNome'       => 'name',
            'xFant'       => 'name',
            'CNPJ'        => '0000000000000',
            'telefone'    => '(000) 0000-0000',
            'endereco'    => 'adress'
        ]);

You can see the seeder is only filling some columns, so running it I will get the same error as before, asking for each column missing. I can aways fill the missing columns with blank spaces and it will run just fine, but I need to understand why it's happening and if it will interfere with the POST methods and sorts in my code too.

CodePudding user response：

I learned a thing or two trying to figure this problem out, so migh as well share with anyone interested. Feel free to correct me if I say anything wrong, I will gladly edit my answer.

MySQL and PostgreSQL have a few differences dealing with the NOT NULL constraint, and my code was not considering those differences.

While trying to seed a MySQL database with let's say, this code:

$this->db->table('configuracoes')->insert([
            'nome_do_app' => 'App',
            'tema'        => '4',
            'xNome'       => 'name',
            'xFant'       => 'name',
            'CNPJ'        => '0000000000000',
            'telefone'    => '(000) 0000-0000',
            'endereco'    => 'adress'
        ]);

Anything else left out the seeder will be given a NULL value, and MySQL won't complain about it, even if the fields are defined as NOT NULL. While I'm not sure how it works in depth, this is what I could see during the time trying to resolve this issue.

Another thing that's different about PostgreSQL compared with MySQL, is the date field type. While leaving this field NULL in a MySQL database, it would automatically set it to 0000-00-00 00:00:00:000. But PostgreSQL won't accept such value, neither NULL, of course. Unless you tell Postgre to accept a NULL value in that field, that would be just an empty field like the others. My code wasn't taking this change in consideration either.

With PostgreSQL, if a field is set to NOT NULL, you can't simply tell Postgre to insert a NULL value, not even manually do so. And my entire database was set to NOT NULL, because it would never be a problem to MySQL.

The solution: I reviewed all my migrations, and only left NOT NULL in the fields that actually needed a value no matter what. Then while seeding the table, I took the NOT NULL fields in consideration and manually inserted a default value, like a space or a zero.

Thank you, everyone that tried to help!

PSA: I'm not the one who created this database from the start, I'm just mainteining and continuing a project made by someone else. So I can't really say anything about the choices made to structure it that way.