Postgres array support?

Yii 2.0 General Discussions
1

Hi.

I need to use postgres arrays, but how can I add the array type to a migration? Then save data to postgres array when I save the model?

2

Hi blaces!

As PostgreSQL uses a kind of type "text" to handle columns defined as array (I do prefer data normalization in very most cases), you can use something like that:




<?php


use yii\db\Migration;

use yii\db\Schema;


class m160518_115010_tbl_empsal extends Migration

{

    public function safeUp()

    {

        $this->createTable('tbl_empsal', [

            'id' => Schema::TYPE_PK,

            'name' => Schema::TYPE_STRING . ' NOT NULL',

            'pay_by_quarter' => Schema::TYPE_TEXT . '[]',

	    'schedule' => Schema::TYPE_TEXT . '[]',			

        ]);


	$this->insert('tbl_empsal', [

            'name' => 'Bill',

            'pay_by_quarter' => '{10000, 10000, 10000, 10000}',

            'schedule' => '{{"meeting", "lunch"}, {"training", "presentation"}}',

        ]);

	$this->insert('tbl_empsal', [

            'name' => 'Carol',

            'pay_by_quarter' => '{20000, 25000, 25000, 25000}',

            'schedule' => '{{"breakfast", "consulting"}, {"meeting", "lunch"}}',

        ]);

        

    }


    public function safeDown()

    {

        // ....

        $this->dropTable('tbl_empsal');

        return true;

    }


}

Please note the concatenation used: Schema::TYPE_TEXT . ‘[]’

Best wishes!

3

Thanks for the code :)

If I want to create an integer array then can I use the simple []?

Yes of course I prefer normalization when the primary data relation important, but sometimes there are some optional not important data and for this I just use the arrays and it gives me better performance then the pivot tables.