Yii2: Alter column, set as NOT NULL and Set Default value, PostgreSQL.

[clue-wiz]

Hello,

In user table I have column: status currently NULL is set and default value not set

This is how the migration looks:

$this->alterColumn('user', 'status', $this->string()->notNull()->defaultValue(10));

and this is the error:

SQLSTATE[42601]: Syntax error: 7 ERROR: syntax error at or near "NOT"
LINE 1: ... TABLE "user" ALTER COLUMN "status" TYPE smallint NOT NULL D...
^
The SQL being executed was: ALTER TABLE "user" ALTER COLUMN "status" TYPE smallint NOT NULL DEFAULT 10

And

SQLSTATE[42601]: Syntax error: 7 ERROR: syntax error at or near "DEFAULT"
LINE 1: ... TABLE "user" ALTER COLUMN "status" TYPE smallint DEFAULT 10
^
The SQL being executed was: ALTER TABLE "user" ALTER COLUMN "status" TYPE smallint DEFAULT 10

Of course, I can change the column manually:

ALTER TABLE "user" ALTER COLUMN "status" ,ALTER COLUMN "status" SET NOT NULL,ALTER COLUMN "status" SET DEFAULT NULL;

but I'm interested why this doesn't work.

| Q | A |
| --- | --- |
| Yii version | 2.0.6 |
| PHP version | 5.5.9 |
| Operating system | Linux and windows Both |

[akkicode]

@clue-wiz Same issue here the following from the code doesn't work
$this->string()->notNull()->defaultValue(10));

But everything seems fine when done statically from the database with the query.

@samdark Any suggestions will be helpful

[samdark]

@akkiCode do you mean that exactly same SQL executes well directly?

[akkicode]

@samdark Yes that's the exact SQL query that works in the database. With exact syntax in pgsql.
And this is the only issue for pg well as it works perfect in mysql.
ALTER TABLE "user" ALTER COLUMN "status" ,ALTER COLUMN "status" SET NOT NULL,ALTER COLUMN "status" SET DEFAULT NULL;

The issue is same in both the versions of Yii 2.0.6 and 2.0.9

[angrybrad]

Just ran into this as well and can verify using the multiple ALTER COLUMN syntax fixes it: http://stackoverflow.com/a/16197615/684

[samdark]

@takobell thanks for confirming it. Would you like to make a pull request?

[akkicode]

@takobell thanks mate for the answer but this is again the static task. And this is solved personally with such static queries but this is not the solution i was looking for.
@samdark Needed the solution for database independent system where any database can work with the same line of code without any kind of staticness or changes for particular database.

[MaksimKiselev]

@samdark хм... Глянул код и пока что единственным решением вижу в районе этого метода https://github.com/yiisoft/yii2/blob/master/framework/db/pgsql/QueryBuilder.php#L218-L227 строить запрос который будет менять тип и ставить/дропать null/default.

Если такое решение нормальное, то готов решить задачу.

[clue-wiz]

@samdark As by the suggestions suggested above by many such as @MKiselev nothing works.
below is the tried code and its output for postgresql where the default not worked.
The same thing goes for NOT NULL.

if (!preg_match('/^(DROP|SET|RESET|NOT NULL|DEFAULT)\s+/i', $type)) {
            $type = 'TYPE ' . $this->getColumnType($type);
}

SQLSTATE[42601]: Syntax error: 7 ERROR: syntax error at or near "DEFAULT"
LINE 1: ...E "core_model" ALTER COLUMN "active" TYPE boolean DEFAULT TR...
^
The SQL being executed was: ALTER TABLE "core_model" ALTER COLUMN "active" TYPE boolean DEFAULT TRUE

@samdark This is the applied and tried part of code which works perfectly with Defaults and Not Null.
which can be altered in https://github.com/yiisoft/yii2/blob/master/framework/db/pgsql/QueryBuilder.php#L218-L227

if (strpos($type,'DEFAULT')) {
            $type =substr_replace($type, ',ALTER COLUMN '. $this->db->quoteColumnName($column) . ' SET DEFAULT ' , strpos($type, 'DEFAULT'),strlen('DEFAULT'));
}   

This is the perfect , applied and tested code for the type errors in QueryBuilder. Where this makes the database independent queries.
( Tried and Tested in both pgsql & mysql )

[ruikc]

2.0.14 has the question also?
gii create the code with postgresql

   /**
     * @inheritdoc
     */
    public function rules()
    {
        return [
            [['classify_id', 'user_id', 'start_time', 'end_time', 'image', 'status', 'created_at', 'updated_at'], 'default', 'value' => null],

        ];
    }

default value=>null

[ruikc]

why postgresql

if ($driverName === 'pgsql' && $type === 'integer') {
      $rules[] = "[['" . implode("', '", $columns) . "'], 'default', 'value' => null]";
 }