Cannot create migration to drop default value of a Postgres column

[starrychloe]

What steps will reproduce the problem?

// remove default values to force setting network_id
$this->alterColumn('account', 'network_id', $this->integer()->notNull());
$this->alterColumn('account', 'network_id', $this->integer()->dropDefault()); # this function doesn't 
exist

What is the expected result?

Drop the default value

What do you get instead?

    > alter column network_id in table account to integer NOT NULL ...Exception 'yii\db\Exception' with message 'SQLSTATE[42601]: Syntax error: 7 ERROR:  syntax error at or near "NOT"
LINE 1: ...LE "account" ALTER COLUMN "network_id" TYPE integer NOT NULL
                                                               ^
The SQL being executed was: ALTER TABLE "account" ALTER COLUMN "network_id" TYPE integer NOT NULL'

Additional info

http://www.yiiframework.com/doc-2.0/yii-db-migration.html#alterColumn()-detail
https://www.postgresql.org/docs/current/static/sql-altertable.html
ALTER [ COLUMN ] column_name DROP DEFAULT

Q	A
Yii version	2.0.8
PHP version	5.6
Operating system	Win 8.1
Postgres	9.5

[samdark]

Looks like DROP DEFAULT is supported by MySQL and PostgreSQL.

SQLite doesn't support altering table schema.

When it comes to MSSQL, it's a bit more complicated: http://stackoverflow.com/questions/10777353/drop-default-constraint-on-a-column-in-tsql

Not sure about Oracle.

[starrychloe]

Also trying to remove not null by altering the column causes a problem.

Yii::$app->db->createCommand('alter table sitelink alter column campaign_id drop not null')->execute();
// $this->alterColumn('sitelink', 'campaign_id', $this->bigInteger()); // remove not null

Just using alterColumn() does nothing.

ALTER [ COLUMN ] column_name { SET | DROP } NOT NULL

[Carw]

Same problem.

$this->alterColumn('provider', 'alias', $this->string(255)->notNull()->unique());

Error:

LINE 1: ..."provider" ALTER COLUMN "alias" TYPE varchar(255) NOT NULL U...
                                                             ^
The SQL being executed was: ALTER TABLE "provider" ALTER COLUMN "alias" TYPE varchar(255) NOT NULL UNIQUE'

Correct SQL code:

ALTER TABLE "provider" ALTER COLUMN "alias" TYPE varchar(255),
ALTER COLUMN "alias" SET NOT NULL, ADD UNIQUE ("alias");